Project Description

Harry Potter Books Popularity

MAT 259A, 2018

Ming-yu Lin

Concept

In this assignment, I chose Harry Potter series to show the relationship of checkout times between different years and different books. Since Harry Potter series has seven books, I chose the seven classic colors in the rainbow. For the 2D design, I took monthly checkout times data to form a seven by ninty-six matrix. I had two factors to reflect the numbers of checkout times which were transparency of colors and the size of the matrix cell. To have more interactive functionalities and specific numbers for each year in the plot, I added a function that if user click on a specific month, the plot will be redrawn into histogram.

Query

This assignment has two version.
First version for year data:

SELECT
    title, COUNT(title) AS COUNTS,
    SUM(CASE
    WHEN (YEAR(cout) = 2005) THEN 1
    ELSE 0
    END) AS '2005',
    SUM(CASE
    WHEN (YEAR(cout) = 2006) THEN 1
    ELSE 0
    END) AS '2006',
    SUM(CASE
    WHEN (YEAR(cout) = 2007) THEN 1
    ELSE 0
    END) AS '2007',
    SUM(CASE
    WHEN (YEAR(cout) = 2008) THEN 1
    ELSE 0
    END) AS '2008',
    SUM(CASE
    WHEN (YEAR(cout) = 2009) THEN 1
    ELSE 0
    END) AS '2009',SUM(CASE
    WHEN (YEAR(cout) = 2010) THEN 1
    ELSE 0
    END) AS '2010',
    SUM(CASE
    WHEN (YEAR(cout) = 2011) THEN 1
    ELSE 0
    END) AS '2011',
    SUM(CASE
    WHEN (YEAR(cout) = 2012) THEN 1
    ELSE 0
    END) AS '2012',
    SUM(CASE
    WHEN (YEAR(cout) = 2013) THEN 1
    ELSE 0
    END) AS '2013',
    SUM(CASE
    WHEN (YEAR(cout) = 2014) THEN 1
    ELSE 0
    END) AS '2014',
    SUM(CASE
    WHEN (YEAR(cout) = 2015) THEN 1
    ELSE 0
    END) AS '2015',
    SUM(CASE
    WHEN (YEAR(cout) = 2016) THEN 1
    ELSE 0
    END) AS '2016'
    FROM
    spl_2016.inraw
WHERE
    ( title = "Harry Potter and the Order of the Phoenix" OR 
    title = "Harry Potter and the half blood prince" OR
    title = "Harry Potter and the deathly hallows" OR
    title = "Harry Potter and the chamber of secrets" OR
    title = "Harry Potter and the sorcerers stone" OR
    title = "Harry Potter and the prisoner of Azkaban" OR
    title = "Harry Potter and the goblet of fire" ) AND
    itemtype LIKE "%bk" AND 
    title NOT LIKE "%soundtrack"
GROUP BY title
ORDER BY title;

Second updated version for monthly data:

SELECT
    title, COUNT(title) AS COUNTS,
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 1) THEN 1
    ELSE 0
    END) AS '2008-1',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 2) THEN 1
    ELSE 0
    END) AS '2008-2',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 3) THEN 1
    ELSE 0
    END) AS '2008-3',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 4) THEN 1
    ELSE 0
    END) AS '2008-4',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 5) THEN 1
    ELSE 0
    END) AS '2008-5',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 6) THEN 1
    ELSE 0
    END) AS '2008-6',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 7) THEN 1
    ELSE 0
    END) AS '2008-7',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 8) THEN 1
    ELSE 0
    END) AS '2008-8',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 9) THEN 1
    ELSE 0
    END) AS '2008-9',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 10) THEN 1
    ELSE 0
    END) AS '2008-10',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 11) THEN 1
    ELSE 0
    END) AS '2008-11',
    SUM(CASE
    WHEN (YEAR(cout) = 2008 and Month(cout) = 12) THEN 1
    ELSE 0
    END) AS '2008-12',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 1) THEN 1
    ELSE 0
    END) AS '2009-1',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 2) THEN 1
    ELSE 0
    END) AS '2009-2',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 3) THEN 1
    ELSE 0
    END) AS '2009-3',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 4) THEN 1
    ELSE 0
    END) AS '2009-4',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 5) THEN 1
    ELSE 0
    END) AS '2009-5',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 6) THEN 1
    ELSE 0
    END) AS '2009-6',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 7) THEN 1
    ELSE 0
    END) AS '2009-7',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 8) THEN 1
    ELSE 0
    END) AS '2009-8',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 9) THEN 1
    ELSE 0
    END) AS '2009-9',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 10) THEN 1
    ELSE 0
    END) AS '2009-10',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 11) THEN 1
    ELSE 0
    END) AS '2009-11',
    SUM(CASE
    WHEN (YEAR(cout) = 2009 and Month(cout) = 12) THEN 1
    ELSE 0
    END) AS '2009-12',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 1) THEN 1
    ELSE 0
    END) AS '2010-1',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 2) THEN 1
    ELSE 0
    END) AS '2010-2',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 3) THEN 1
    ELSE 0
    END) AS '2010-3',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 4) THEN 1
    ELSE 0
    END) AS '2010-4',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 5) THEN 1
    ELSE 0
    END) AS '2010-5',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 6) THEN 1
    ELSE 0
    END) AS '2010-6',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 7) THEN 1
    ELSE 0
    END) AS '2010-7',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 8) THEN 1
    ELSE 0
    END) AS '2010-8',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 9) THEN 1
    ELSE 0
    END) AS '2010-9',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 10) THEN 1
    ELSE 0
    END) AS '2010-10',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 11) THEN 1
    ELSE 0
    END) AS '2010-11',
    SUM(CASE
    WHEN (YEAR(cout) = 2010 and Month(cout) = 12) THEN 1
    ELSE 0
    END) AS '2010-12',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 1) THEN 1
    ELSE 0
    END) AS '2011-1',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 2) THEN 1
    ELSE 0
    END) AS '2011-2',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 3) THEN 1
    ELSE 0
    END) AS '2011-3',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 4) THEN 1
    ELSE 0
    END) AS '2011-4',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 5) THEN 1
    ELSE 0
    END) AS '2011-5',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 6) THEN 1
    ELSE 0
    END) AS '2011-6',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 7) THEN 1
    ELSE 0
    END) AS '2011-7',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 8) THEN 1
    ELSE 0
    END) AS '2011-8',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 9) THEN 1
    ELSE 0
    END) AS '2011-9',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 10) THEN 1
    ELSE 0
    END) AS '2011-10',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 11) THEN 1
    ELSE 0
    END) AS '2011-11',
    SUM(CASE
    WHEN (YEAR(cout) = 2011 and Month(cout) = 12) THEN 1
    ELSE 0
    END) AS '2011-12',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 1) THEN 1
    ELSE 0
    END) AS '2012-1',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 2) THEN 1
    ELSE 0
    END) AS '2012-2',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 3) THEN 1
    ELSE 0
    END) AS '2012-3',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 4) THEN 1
    ELSE 0
    END) AS '2012-4',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 5) THEN 1
    ELSE 0
    END) AS '2012-5',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 6) THEN 1
    ELSE 0
    END) AS '2012-6',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 7) THEN 1
    ELSE 0
    END) AS '2012-7',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 8) THEN 1
    ELSE 0
    END) AS '2012-8',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 9) THEN 1
    ELSE 0
    END) AS '2012-9',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 10) THEN 1
    ELSE 0
    END) AS '2012-10',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 11) THEN 1
    ELSE 0
    END) AS '2012-11',
    SUM(CASE
    WHEN (YEAR(cout) = 2012 and Month(cout) = 12) THEN 1
    ELSE 0
    END) AS '2012-12',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 1) THEN 1
    ELSE 0
    END) AS '2013-1',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 2) THEN 1
    ELSE 0
    END) AS '2013-2',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 3) THEN 1
    ELSE 0
    END) AS '2013-3',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 4) THEN 1
    ELSE 0
    END) AS '2013-4',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 5) THEN 1
    ELSE 0
    END) AS '2013-5',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 6) THEN 1
    ELSE 0
    END) AS '2013-6',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 7) THEN 1
    ELSE 0
    END) AS '2013-7',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 8) THEN 1
    ELSE 0
    END) AS '2013-8',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 9) THEN 1
    ELSE 0
    END) AS '2013-9',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 10) THEN 1
    ELSE 0
    END) AS '2013-10',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 11) THEN 1
    ELSE 0
    END) AS '2013-11',
    SUM(CASE
    WHEN (YEAR(cout) = 2013 and Month(cout) = 12) THEN 1
    ELSE 0
    END) AS '2013-12',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 1) THEN 1
    ELSE 0
    END) AS '2014-1',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 2) THEN 1
    ELSE 0
    END) AS '2014-2',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 3) THEN 1
    ELSE 0
    END) AS '2014-3',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 4) THEN 1
    ELSE 0
    END) AS '2014-4',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 5) THEN 1
    ELSE 0
    END) AS '2014-5',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 6) THEN 1
    ELSE 0
    END) AS '2014-6',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 7) THEN 1
    ELSE 0
    END) AS '2014-7',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 8) THEN 1
    ELSE 0
    END) AS '2014-8',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 9) THEN 1
    ELSE 0
    END) AS '2014-9',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 10) THEN 1
    ELSE 0
    END) AS '2014-10',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 11) THEN 1
    ELSE 0
    END) AS '2014-11',
    SUM(CASE
    WHEN (YEAR(cout) = 2014 and Month(cout) = 12) THEN 1
    ELSE 0
    END) AS '2014-12',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 1) THEN 1
    ELSE 0
    END) AS '2015-1',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 2) THEN 1
    ELSE 0
    END) AS '2015-2',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 3) THEN 1
    ELSE 0
    END) AS '2015-3',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 4) THEN 1
    ELSE 0
    END) AS '2015-4',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 5) THEN 1
    ELSE 0
    END) AS '2015-5',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 6) THEN 1
    ELSE 0
    END) AS '2015-6',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 7) THEN 1
    ELSE 0
    END) AS '2015-7',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 8) THEN 1
    ELSE 0
    END) AS '2015-8',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 9) THEN 1
    ELSE 0
    END) AS '2015-9',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 10) THEN 1
    ELSE 0
    END) AS '2015-10',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 11) THEN 1
    ELSE 0
    END) AS '2015-11',
    SUM(CASE
    WHEN (YEAR(cout) = 2015 and Month(cout) = 12) THEN 1
    ELSE 0
    END) AS '2015-12'
FROM
spl_2016.inraw
WHERE
    ( title = "Harry Potter and the Order of the Phoenix" OR 
    title = "Harry Potter and the half blood prince" OR
    title = "Harry Potter and the deathly hallows" OR
    title = "Harry Potter and the chamber of secrets" OR
    title = "Harry Potter and the sorcerers stone" OR
    title = "Harry Potter and the prisoner of Azkaban" OR
    title = "Harry Potter and the goblet of fire" ) AND
    itemtype LIKE "%bk" AND 
    title NOT LIKE "%soundtrack"
GROUP BY title
ORDER BY title;

Preliminary sketches

The idea was inspired by the keyboard from Razer.

Process

The process for version one.

Final result

In the first version, there was 12 years data. It was interesting to find that seven books share with the same trend in 12 years. That is to say, the number of checkout for every installment has the same fluctuation pattern. In addition, I manually sorted every installment by published time. The bottom one, Sorcerers stones, was the first book and the top one, Deathly hallows, was the last book. The plot showed that there was no big difference in number of checkout for books published before 2005. Furthermore, seven books had similar numbers after 2012. One thing should be mentioned was that the number for the first book remained high compared to other books.

In the second version, there was only 8 years but monthly data. It was more clear that the number of times of checkout remained low after July 2012.

Code

All work is developed within Processing
Source Code + Data