For this project, I asked "are there any patterns in check-out counts and estimated purchase dates for anti-racist literature?"
I proceeded in two steps: First, I scraped multiple lists of anti-racist book titles from the web using a custom web scraper.
I then used an API to convert these titles into a format that the Seattle Public Library database would understand.
Next, I used this
cleaned list of titles to code an mySQL query that extracts these titles by year, by week, and a query that checks which items have been checked out
for the first time in 2020.
Overall checkouts by year 2006-today:
# This query is to see how many anti-racist items were checked out by year in 2020
SELECT YEAR(cout), title, count(title) AS cout_by_year
FROM spl_2016.outraw
WHERE title IN (anti-racist-books)
GROUP BY
YEAR(cout),
title;
Another interesting question is whether we can see a change in trends due to the enormous
BLM protests in 2020. To this end, I aggregated the books checked out in 2020 by week with
the following query:
# This query is to see how many anti-racist items were checked out by week in 2020
SELECT WEEK(cout), title, count(title) AS cout_by_week
FROM spl_2016.outraw
WHERE title IN (anti-racist-books)
AND
YEAR(cout) = 2020
GROUP BY
WEEK(cout),
title;
One thing that could be used to see whether the library showed an increased interest in antiracist literature is to see whether they purchased more such publications than in the previous
year. To do that, I run the following query:
# This query is to see how many anti-racist items were first checked out in 2020
# https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sqlresults
# axiac's response
SELECT o.* #itemNumber, title, cout
FROM spl_2016.outraw o # 'o' from 'earliest checkout date of itemNumber'
LEFT JOIN spl_2016.outraw b # 'b' from 'earlier checkout date'
ON o.itemNumber = b.itemNumber AND o.cout > b.cout
WHERE b.cout is NULL # earlier checkout date not found
AND
o.title IN (anti-racist-books)