*****************************************************************************************
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
title ------------ Eminence Front
Solution postgres
SELECT S.title
FROM song_plays d
INNER JOIN song S
ON S.song_id = d.song_id
WHERE d.country = 'US'
AND d.date = '2021-01-01'
ORDER BY num_plays DESC
LIMIT 1
Explanation
This query selects the title of the song that was played the most in the US on January 1st, 2021. It does this by joining the song_plays table with the song table on the song_id column. It then filters the results to only include plays that occurred in the US on January 1st, 2021. The results are sorted in descending order based on the number of plays, and the limit is set to 1, meaning only the top result is returned.
Expected results