88. Top song report

medium spotify


  • ******************************************************************************************************2021-01-01 - 2021-01-07
  • ************************************
  • *********************************************
  • *****************************************

Table 1: song

  col_name   | col_type
   song_id   | bigint
   title     | varchar(1000)
   artist_id | bigint

Table 2: song_plays

Number of times a song is played (streamed), aggregated on daily basis.

  col_name   | col_type
 date        | date
 country     | varchar(2)
 song_id     | bigint
 num_plays   | bigint

Sample results

 country |        title        | num_plays
 AU      | Hot                 |  11734009
 CA      | Bad Guy             |   5430133
 CN      | Dynamite            |   4758076
 DE      | High Fashion        |   8802157
 ES      | Yummy               |   4819624
 FR      | Does To Me          |   9416258
 IN      | Laugh Now Cry Later |  10754927

Solution postgres

WITH song_plays AS (
	    MAX(S.title) AS title,	    	    
	    SUM(num_plays) num_plays
	FROM song_plays P
	ON P.song_id = S.song_id
	WHERE P.date >= '2021-01-01'
	AND P.date <= '2021-01-07'	
	GROUP BY S.song_id, P.country
song_ranking AS(
	SELECT song_id, title, country, num_plays, ROW_NUMBER() OVER(PARTITION BY country ORDER BY num_plays DESC) AS ranking
	FROM song_plays	
SELECT country, title, num_plays
FROM song_ranking
WHERE ranking = 1;


This query is selecting data from two tables, song_plays and song, and creating two temporary tables with the "WITH" statement: song_plays and song_ranking.

The song_plays table is created by joining the song and song_plays tables on the song_id column and filtering the results to only include plays that occurred between January 1st and January 7th, 2021. The table is grouped by song_id and country, and a new column called num_plays is created by summing the number of plays for each song.

The song_ranking table is created by selecting the song_id, title, country, num_plays columns from the song_plays table and adding a new column called ranking. The ranking column is created using the ROW_NUMBER() function, which assigns a unique rank to each row based on the country and the number of plays for that song in that country. The ranking is determined by ordering the num_plays column in descending order within each country.

Finally, the query selects the country, title, and num_plays columns from the song_ranking table where the ranking is equal to 1. This returns the most played song in each country during the period between January 1st and January 7th,

Expected results

More Spotify questions

ID Title Level FTPR
86 Top song in the US spotify easy
87 Top song in the US and UK spotify medium
89 Top 5 artists in the US spotify medium
90 Top artist report spotify medium
91 same day friend request acceptance rate spotify easy
92 Acceptance rate first week of 2021 spotify easy