Spotify: Wrapped Top Artist of Every Listener — SQL Problem
For the year-end Wrapped story, every listener's card opens with the artist they spent the most time with in 2024.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #933
Problem statement
For the year-end Wrapped story, every listener's card opens with the artist they spent the most time with in 2024. Only plays whose played_at falls in 2024 count, and time is the sum of ms_played.
Return one row per listener with at least one 2024 play, with the columns listener_id, top_artist (the artist's name) and minutes_played — that artist's 2024 listening time in minutes (ms / 60000), rounded to 1 decimal place. When two artists tie on time, the one whose name comes first alphabetically wins. Return the rows in any order.
A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.
Tables
Table: Artist
| Column | Type |
|---|---|
| artist_id | int |
| artist_name | varchar |
Primary key: artist_id.
One row per artist; names are unique.
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| listener_id | int |
| artist_id | int |
| played_at | datetime |
| ms_played | int |
Primary key: play_id.
One row per play of a track, credited to the track's main artist.
Examples
Example 1
Artist
| artist_id | artist_name |
|---|---|
| 1 | Aurora Lanes |
| 2 | Bombay Bassline |
| 3 | Coral Static |
Play
| play_id | listener_id | artist_id | played_at | ms_played |
|---|---|---|---|---|
| 1 | 7 | 1 | 2024-02-11 08:30:00 | 180000 |
| 2 | 7 | 2 | 2024-03-01 19:05:00 | 240000 |
| 3 | 7 | 1 | 2024-12-31 23:59:00 | 60000 |
| 4 | 8 | 3 | 2023-12-31 22:00:00 | 900000 |
| 5 | 8 | 2 | 2024-01-01 00:10:00 | 126000 |
| 6 | 9 | 3 | 2024-06-14 10:00:00 | 300000 |
| 7 | 9 | 2 | 2024-06-15 10:00:00 | 300000 |
| 8 | 10 | 1 | 2025-01-01 00:00:00 | 200000 |
Output
| listener_id | top_artist | minutes_played |
|---|---|---|
| 7 | Aurora Lanes | 4 |
| 8 | Bombay Bassline | 2.1 |
| 9 | Bombay Bassline | 5 |
How to solve Spotify: Wrapped Top Artist of Every Listener
This is a top-1 per group over an aggregate. Step one sums ms_played per listener and artist, keeping only plays with played_at in 2024 — the half-open range >= '2024-01-01' AND < '2025-01-01' keeps a play at 23:59 on New Year's Eve and drops one at midnight after it.
Step two ranks each listener's artists with ROW_NUMBER() OVER (PARTITION BY listener_id ORDER BY ms DESC, artist_name) and keeps rank 1. Because the order includes the name, a tie on time — listener 9 spent exactly five minutes with two artists — resolves to the alphabetically first artist, and exactly one row per listener survives. RANK() would keep both tied artists.
The alternative keeps a total when no other total of the same listener beats it, with the same two-level comparison spelled out in NOT EXISTS. A listener whose only plays are from 2023 or 2025 has no totals and no row. The minutes are ms / 60000 rounded to one place. Both approaches aggregate once; the window version then sorts each listener's few artists, the anti-join compares pairs of them.
Reference solution (MySQL)
WITH totals AS (
SELECT p.listener_id, a.artist_name, SUM(p.ms_played) AS ms
FROM Play p
JOIN Artist a ON a.artist_id = p.artist_id
WHERE p.played_at >= '2024-01-01' AND p.played_at < '2025-01-01'
GROUP BY p.listener_id, a.artist_id, a.artist_name
), ranked AS (
SELECT listener_id, artist_name, ms,
ROW_NUMBER() OVER (PARTITION BY listener_id ORDER BY ms DESC, artist_name) AS rn
FROM totals
)
SELECT listener_id, artist_name AS top_artist, ROUND(ms / 60000, 1) AS minutes_played
FROM ranked
WHERE rn = 1Another way
WITH totals AS (SELECT p.listener_id, a.artist_name, SUM(p.ms_played) AS ms FROM Play p JOIN Artist a ON a.artist_id = p.artist_id
WHERE YEAR(p.played_at) = 2024 GROUP BY p.listener_id, a.artist_id, a.artist_name)
SELECT t.listener_id, t.artist_name AS top_artist, ROUND(t.ms / 60000, 1) AS minutes_played
FROM totals t
WHERE NOT EXISTS (SELECT 1 FROM totals o WHERE o.listener_id = t.listener_id
AND (o.ms > t.ms OR (o.ms = t.ms AND o.artist_name < t.artist_name)))← Spotify: Free Listeners Who Hit the Hourly Skip Cap · Spotify: Collaborative Playlists Only the Owner Edits →