Top 5 Artists
ASKED IN INTERVIEWSpotify
Problem Statement
Spotify wants to recognize the most successful artists on their platform.
Write an SQL query to find the top 5 artists whose songs appear most frequently in the Top 10 of the global_song_rank table.
Rules:
- Only consider songs with a rank of 10 or better (1 to 10).
- If multiple artists have the same number of Top 10 appearances, they should receive the same rank.
- Use DENSE_RANK() so that if there are ties, the next rank in the sequence is not skipped.
- Output the artist_name and the artist_rank.
- Results must be sorted by artist_rank in ascending order, then by artist_name in ascending order.
Tests your understanding of
Ranking, Window Functions, JOIN and Aggregation
Input Tables
artists
| artist_id(INTEGER) | artist_name(VARCHAR) |
|---|---|
| 1 | Artist A |
| 2 | Artist B |
| 3 | Artist C |
| 4 | Artist D |
| 5 | Artist E |
| 6 | Artist F |
songs
| song_id(INTEGER) | artist_id(INTEGER) |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 201 | 2 |
| 301 | 3 |
| 401 | 4 |
| 501 | 5 |
| 601 | 6 |
global_song_rank
| day(INTEGER) | song_id(INTEGER) | rank(INTEGER) |
|---|---|---|
| 1 | 101 | 1 |
| 1 | 102 | 2 |
| 1 | 201 | 3 |
| 2 | 101 | 1 |
| 2 | 301 | 5 |
| 3 | 401 | 10 |
| 3 | 501 | 10 |
| 3 | 601 | 11 |
Expected Output
| artist_name(VARCHAR) | artist_rank(INTEGER) |
|---|---|
| Artist A | 1 |
| Artist B | 2 |
| Artist C | 2 |
| Artist D | 2 |
| Artist E | 2 |
Tags
MediumASKED IN INTERVIEWRankingWindow FunctionsJOINAggregation
20-25 min
40%