logo

Back to questions

Top 5 Artists

Medium

Assume there are three Spotify tables containing information about the artists, songs, and music charts. Write a query to determine the top 5 artists whose songs appear in the Top 10 of the table the highest number of times. From now on, we'll refer to this ranking number as "song appearances".

Output the top 5 artist names in ascending order along with their song appearances ranking (not the number of song appearances, but the rank of who has the most appearances). The order of the rank should take precedence.

For example, Ed Sheeran's songs appeared 5 times in Top 10 list of the global song rank table; this is the highest number of appearances, so he is ranked 1st. Bad Bunny's songs appeared in the list 4, so he comes in at a close 2nd.

Assumptions:

  • If two artists' songs have the same number of appearances, the artists should have the same rank.
  • The rank number should be continuous (1, 2, 2, 3, 4, 5) and not skipped (1, 2, 2, 4, 5).

Table:

Column NameType
artist_idinteger
artist_namevarchar

Example Input:

artist_idartist_name
101Ed Sheeran
120Drake

Table:

Column NameType
song_idinteger
artist_idinteger

Example Input:

song_idartist_id
45202101
19960120

Table:

Column NameType
dayinteger (1-52)
song_idinteger
rankinteger (1-1,000,000)

Example Input:

daysong_idrank
1452025
3452022
1199603
91996015

Example Output:

artist_nameartist_rank
Ed Sheeran1
Drake2

Explanation

Ed Sheeran's song appeared twice in the Top 10 list of global song rank while Drake's song is only listed once. Therefore, Ed is ranked #1 and Drake is ranked #2.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.