sqlqueries.py

# finds top 5 most listened to tracks
"""
    SELECT trackName, artistName, SUM(msPlayed) / 60000 AS totalMinutes
    FROM streaming_history
    GROUP BY trackName, artistName
    HAVING trackName IS NOT NULL
    ORDER BY totalMinutes DESC
    LIMIT 5; 
"""

# finds total listening time
"""
    SELECT SUM(msPlayed) / 3600000 AS totalHours
    FROM streaming_history;
"""

# finds top 5 most played artists
"""
    SELECT artistName, SUM(msPlayed) / 60000 AS totalMinutes
    FROM streaming_history
    GROUP BY artistName
    HAVING artistName IS NOT NULL
    ORDER BY totalMinutes DESC
    limit 5;
"""

# finds top 5 most played songs from your top played artist
"""
    SELECT trackName, SUM(msPlayed) / 60000 AS totalMinutes
    FROM streaming_history
    WHERE artistName = (SELECT artistName
                        FROM streaming_history
                        GROUP BY artistName
                        ORDER BY SUM(msPlayed) DESC LIMIT 1)
    GROUP BY trackName
    ORDER BY totalMinutes DESC
    limit 5;
"""

# finds most played songs during summer months (05, 06, 07, 08) (im including may bc its usually when i start to feel happier in my music LOl)
"""
    WITH MonthlyPlayCounts AS (
        -- filter for summer months, year >= 2020, exclude skipped and null artist, and count plays
        SELECT 
            EXTRACT(MONTH FROM endtime) AS summer_month,
            artistname,
            trackname,
            COUNT(*) AS play_count
        FROM 
            streaming_history
        WHERE 
            EXTRACT(MONTH FROM endtime) IN (5, 6, 7, 8)
            AND endtime >= '2020-01-01'
            AND skipped = false
            AND artistname IS NOT null
        GROUP BY 
            EXTRACT(MONTH FROM endtime),
            artistname,
            trackname
    ),
    RankedSongs AS (
        -- rank the songs within each month based on play count
        SELECT 
            summer_month,
            artistname,
            trackname,
            play_count,
            DENSE_RANK() OVER (PARTITION BY summer_month ORDER BY play_count DESC) AS rank
        FROM 
            MonthlyPlayCounts
    )
    -- filter for only the top 10 songs per month
    SELECT 
        summer_month,
        rank,
        artistname,
        trackname,
        play_count
    FROM 
        RankedSongs
    WHERE 
        rank <= 10
    ORDER BY 
        summer_month, 
        rank;
"""

# finds the most played songs in the winter months
"""
    WITH MonthlyPlayCounts AS (
        -- filter for winter months, year >= 2020, exclude skipped and null artist, and count plays
        SELECT 
            EXTRACT(MONTH FROM endtime) AS winter_month,
            artistname,
            trackname,
            COUNT(*) AS play_count
        FROM 
            streaming_history
        WHERE 
            EXTRACT(MONTH FROM endtime) IN (11, 12, 1, 2)
            AND endtime >= '2020-01-01'
            AND skipped = false
            AND artistname IS NOT null
        GROUP BY 
            EXTRACT(MONTH FROM endtime),
            artistname,
            trackname
    ),
    RankedSongs AS (
        -- rank the songs within each month based on play count
        SELECT 
            winter_month,
            artistname,
            trackname,
            play_count,
            DENSE_RANK() OVER (PARTITION BY winter_month ORDER BY play_count DESC) AS rank
        FROM 
            MonthlyPlayCounts
    )
    -- filter for only the top 10 songs per month
    SELECT 
        winter_month,
        rank,
        artistname,
        trackname,
        play_count
    FROM 
        RankedSongs
    WHERE 
        rank <= 10
    ORDER BY 
        winter_month, 
        rank;
"""

# finds my top 5 taylor swift songs
"""
    SELECT trackName, SUM(msPlayed) / 60000 AS totalMinutes
    FROM streaming_history
    WHERE artistName LIKE '%Taylor Swift%'
    GROUP BY trackName
    ORDER BY totalMinutes DESC
    LIMIT 5;
"""