***********
- ******************************************************************************************************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 (
SELECT
S.song_id,
P.country,
MAX(S.title) AS title,
SUM(num_plays) num_plays
FROM song_plays P
INNER JOIN song S
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;
Explanation
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