Top track per listener
mediumTop track per listener
Spotify SQL Interview Question
Spotify builds a personal year-in-review around the one track a listener returned to most. Getting it wrong is worse than showing nothing, because the whole feature trades on the listener recognizing themselves in it.
For each listener, return the listener_id, the track_id they played most often, and the number of times they played it as plays.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
spotify_playsTable30 rows
| Column Name | Type |
|---|---|
| play_id | BIGINT |
| listener_id | VARCHAR |
| track_id | VARCHAR |
| played_at | TIMESTAMP |
| ms_played | BIGINT |
spotify_playsExample Input
| play_id | listener_id | track_id | played_at | ms_played |
|---|---|---|---|---|
| 5001 | L01 | T100 | 2024-03-04 08:02:00 | 210000 |
| 5002 | L01 | T104 | 2024-03-04 08:06:00 | 185000 |
| 5003 | L01 | T100 | 2024-03-04 08:10:00 | 198000 |
| 5004 | L01 | T112 | 2024-03-04 09:15:00 | 240000 |
| 5005 | L01 | T104 | 2024-03-04 09:21:00 | 172000 |
| 5006 | L01 | T100 | 2024-03-04 18:40:00 | 205000 |
| 5007 | L01 | T118 | 2024-03-04 18:44:00 | 160000 |
| 5008 | L01 | T112 | 2024-03-04 18:49:00 | 233000 |
| 5026 | L04 | T100 | 2024-03-04 11:00:00 | 204000 |
| 5027 | L04 | T100 | 2024-03-04 11:05:00 | 201000 |
| 5028 | L04 | T104 | 2024-03-04 15:45:00 | 178000 |
| 5029 | L04 | T118 | 2024-03-04 15:52:00 | 162000 |
| 5030 | L04 | T121 | 2024-03-04 15:58:00 | 249000 |
Example Output
| listener_id | track_id | plays |
|---|---|---|
| L01 | T100 | 3 |
| L04 | T100 | 2 |
Explanation
L01 played T100 three times, more than any other track, so that is their top one. L04 played T100 twice and everything else once.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Spotify
Difficulty
medium
Topic
ranking
Language
SQL
Your query
Starting the SQL engine...