1 Advanced MySQL: Frequency Pattern

merttoka
Posts: 15
Joined: Wed Jan 11, 2017 10:42 am

Re: 1b Advanced MySQL: Frequency Pattern

Post by merttoka » Mon Feb 06, 2017 8:45 pm

merttoka wrote:Do Film Adaptations Promote Reading?

In this phase, I am questioning whether film adaptations of particular titles promote their printed counterparts? It is common notion that an average audience of big budget productions later being informed that the story is based on either fiction or nonfiction books. I want to examine when this revelation kicks in and pushes people to read books, and what kinds of parameters play an important role on this timeframe between movie popularity and book resurrection.

Queries:

Code: Select all

SELECT 
    MONTH(c.checkOut) 'Month',
    YEAR(c.checkOut) 'Year',
    COUNT(*) '# Checkouts',
    i.itemType 'Type',
    t.title 'Title'
FROM
    title t
        JOIN
    itemToBib b ON t.bibNumber = b.bibNumber
        JOIN
    itemType i ON b.itemNumber = i.itemNumber
        JOIN
    transactions c ON c.itemNumber = b.itemNumber
WHERE
    t.title LIKE 'game of thrones%'
        AND YEAR(c.checkOut) > 1995
GROUP BY MONTH(c.checkOut) , YEAR(c.checkOut) , i.itemType
HAVING Year > 1995
The year and title are adjusted depending on the item. This example is only for the Game of Thrones. The query joins title, itemToBib, itemType and transactions tables in order to be able to retrieve checkout month and year, the number of checkouts, itemType, and title. The grouping on checkout month, year, and item type enables COUNT(*) to work for a specific month of a year. Also, the wildcard search on the title is performed as only right wildcards because the MySQL server applies indexing to fasten right-wildcard searches but it is particularly vulnerable for the left ones. I ran separate queries to validate accuracy for this assumption and found out that every copy of the book or motion picture is, in fact, starts with the original title. There are some titles that have additional suffixes, such as "episode 1" but this search term can capture those as well.

Results:
twilight.csv
orangeIsTheNewBlack.csv
mazeRunner.csv
hungerGames.csv
gameOfThrones.csv
divergent.csv
Processing times of these queries vary from 200 seconds to 300 seconds. The wildcard optimization increased the speed by a factor of 5.

Analysis:
The general trend of movie checkout vs book checkout relation seems to be that the more a movie becomes popular the quicker its original book attracts attention on itself.

For Hunger Games, the trend reveals that before the movie release people were actively reading the book. After two months from the release, the book checkouts dropped drastically and left its place to DVD and cd checkouts.

As for Game of Thrones, while reading rates progressing slowly but steadily, there is a quick jump when the shows announced. However, it could not maintain this popularity and left its portion to the media disks.

The similar trend is also apparent for the other titles. My main intuition that the motion picture alternatives of the titles could potentially increase the book checkouts proved wrong in the sense that when the alternative is announced, there is indeed a jump in the printed medium checkouts, however, as the time goes by, the motion picture alternatives suppress them and claim their victory over printed medium. I would expect a steady increase in the printed medium since people may want to learn the real story behind the scenario.

Post Reply