The goal is to show the information of the checkout duration and checkout amounts for different dewey classes and subclasses over the past 15 years. For the design, I want the product to be able to show the pattern of popularity and checkout duration within each year and also yearly or seasonal trend on the checkout duration and checkout amounts for different dewey classes and subclasses. The idea of the design is to have z axis as time axis, and to have polygons to show the information for each year or month. Line plots on the time axis shows the trend over years or months. In the design: Vertices to center length of polygon: checkout duration of each class or subclass. Color fill of polygon: the total number of checkouts in that year or month. Value of line plot: checkout duration of each class or subclass. Color of line plot: the total number of checkouts of each class or subclass. To keep the design clean and easy to extract information, I want to use interaction to give users the ability to show or fold the information. When one wants to see the monthly trend, they can click on the polygon of each year and it will unfold and show the monthly data. When one wants to see the subclass information, they can click on each class to show the information for subclasses.

SQL Code 1:

SELECT 
    SUM(CASE
        WHEN deweyClass > 0 AND deweyClass < 100 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 0 AND deweyClass < 100 THEN 1
        ELSE 0
    END) AS 'dewey_000',
    SUM(CASE
        WHEN deweyClass > 100 AND deweyClass < 200 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 100 AND deweyClass < 200 THEN 1
        ELSE 0
    END) AS 'dewey_100',
    SUM(CASE
        WHEN deweyClass > 200 AND deweyClass < 300 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 200 AND deweyClass < 300 THEN 1
        ELSE 0
    END) AS 'dewey_200',
    SUM(CASE
        WHEN deweyClass > 300 AND deweyClass < 400 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 300 AND deweyClass < 400 THEN 1
        ELSE 0
    END) AS 'dewey_300',
    SUM(CASE
        WHEN deweyClass > 400 AND deweyClass < 500 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 400 AND deweyClass < 500 THEN 1
        ELSE 0
    END) AS 'dewey_400',
    SUM(CASE
        WHEN deweyClass > 500 AND deweyClass < 600 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 500 AND deweyClass < 600 THEN 1
        ELSE 0
    END) AS 'dewey_500',
    SUM(CASE
        WHEN deweyClass > 600 AND deweyClass < 700 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 600 AND deweyClass < 700 THEN 1
        ELSE 0
    END) AS 'dewey_600',
    SUM(CASE
        WHEN deweyClass > 700 AND deweyClass < 800 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 700 AND deweyClass < 800 THEN 1
        ELSE 0
    END) AS 'dewey_700',
    SUM(CASE
        WHEN deweyClass > 800 AND deweyClass < 900 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 800 AND deweyClass < 900 THEN 1
        ELSE 0
    END) AS 'dewey_800',
    SUM(CASE
        WHEN deweyClass > 900 AND deweyClass < 1000 THEN TIMESTAMPDIFF(HOUR, cout, cin) / 24.0
        ELSE 0
    END) / SUM(CASE
        WHEN deweyClass > 900 AND deweyClass < 1000 THEN 1
        ELSE 0
    END) AS 'dewey_900'
FROM
    spl_2016.inraw
WHERE
    itemtype LIKE '%bk'
        AND YEAR(cout) >= '2006'
        AND YEAR(cout) <= '2020'
GROUP BY YEAR(cout), MONTH(cout)
ORDER BY YEAR(cout), MONTH(cout) ASC;






SQL Code 2:

SELECT 
    SUM(CASE
        WHEN deweyClass > 000 AND deweyClass < 100 THEN 1
        ELSE 0
    END) AS 'dewey_000',
    SUM(CASE
        WHEN deweyClass > 100 AND deweyClass < 200 THEN 1
        ELSE 0
    END) AS 'dewey_100',
   SUM(CASE
        WHEN deweyClass > 200 AND deweyClass < 300 THEN 1
        ELSE 0
    END) AS 'dewey_200',
   SUM(CASE
        WHEN deweyClass > 300 AND deweyClass < 400 THEN 1
        ELSE 0
    END) AS 'dewey_300',
    SUM(CASE
        WHEN deweyClass > 400 AND deweyClass < 500 THEN 1
        ELSE 0
    END) AS 'dewey_400',
    SUM(CASE
        WHEN deweyClass > 500 AND deweyClass < 600 THEN 1
        ELSE 0
    END) AS 'dewey_500',
    SUM(CASE
        WHEN deweyClass > 600 AND deweyClass < 700 THEN 1
        ELSE 0
    END) AS 'dewey_600',
    SUM(CASE
        WHEN deweyClass > 700 AND deweyClass < 800 THEN 1
        ELSE 0
    END) AS 'dewey_700',
    SUM(CASE
        WHEN deweyClass > 800 AND deweyClass < 900 THEN 1
        ELSE 0
    END) AS 'dewey_800',
    SUM(CASE
        WHEN deweyClass > 900 AND deweyClass < 1000 THEN 1
        ELSE 0
    END) AS 'dewey_900'
FROM
    spl_2016.inraw
WHERE
    itemtype LIKE '%bk'
        AND YEAR(cout) >= '2006'
        AND YEAR(cout) <= '2020'
GROUP BY YEAR(cout), MONTH(cout)
ORDER BY YEAR(cout), MONTH(cout) ASC;
            

Insights:

1. The total number of checkouts went up in the first four years but started to go down after 2009.

2. The checkout duration slowly went down from 2006, and had a big jump down at year 2011. The checkout duration in year 2020 was more than triple of the previous year. This is probably caused by the policy change because of the COVID situation.

3. In general the average checkout duration is longer than the policy of the library. This is a little weird and I don’t have a good explanation for it. Maybe it took a few extra days for the returned item to get checked in?

4. The dewey classes with shortest checkout durations is arts and recreation. The class with longest duration changed from literature to language from year 2011

5. The most popular dewey class is arts and recreation and the lear popular is language.

6. Because of the COVID situation, there are close to zero checkouts from April to July, 2020.

Please put your final images or screen shots here with some explanations.

All work is developed within Processing
Source Code + Data