0

I am working on a project using my daily music listens data from Apple Music from 2018-2022. The table daily_listens consists of the following columns shown in this example:

track_identifier date_played year_played play_duration source_type play_count skip_count track_reference track_description artist song
1127435215 20180410 2018 1102507 IPHONE 6 3 1127435215 Mos Def - Auditorium (feat. Slick Rick) Mos Def Auditorium (feat. Slick Rick)

I am using the columns year_played, play_count and artist in this scenario. play_count is listed for each song so I want to rank each of the artists by the sum of their play_counts and rank the top 5 for each year. I am trying to get a result similar to this example below.

desired_outcome

This is what I have got so far. It gives me the list of all artists with their total play count for each year. However I cannot figure out how to rank them from highest to lowest and limit it to 5 results for each year.

    SELECT RANK() OVER(PARTITION BY year_played ORDER BY total_listens_per_artist) AS ranks,
       *
FROM (
    SELECT DISTINCT artist, 
           year_played,
           SUM(play_count) OVER(PARTITION BY artist) AS counts
    FROM daily_listens 
     ) AS total_listens_per_artist;

Thank you and please let me know if you need any additional information.

Max
  • 1
  • 1

1 Answers1

0
WITH play_count AS (
SELECT 
    artist,
    SUM(play_count) AS counts
FROM daily_listens
GROUP BY artist
)
SELECT * 
FROM play_count 
ORDER BY counts 
LIMIT 5
Tyken
  • 3
  • 2
  • Thanks for your response, however this does not rank the top 5 artist by sum of play_count for each year. If I order the counts DESC in this query then it provides the total play count for the top 5 artists out of all years in the dataset. – Max Jan 04 '23 at 17:01