Anti-Racist Literature in the Seattle Public Library
MAT 259, 2021
Ingmar Sturm
Concept
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.
Query
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)
Final result
These plots show the number of checked-out anti-racist literature books by year and week. The third plot shows the purchases of anti-racist literature in 2019 compared to 2020.