Spotify: Wrapped Top Artist of Every Listener

Medium
933.

Spotify: Wrapped Top Artist of Every Listener

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

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist; names are unique.

Table: Play

ColumnType
play_idint
listener_idint
artist_idint
played_atdatetime
ms_playedint

Primary key: play_id.

One row per play of a track, credited to the track's main artist.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Artist

artist_idartist_name
1Aurora Lanes
2Bombay Bassline
3Coral Static

Play

play_idlistener_idartist_idplayed_atms_played
1712024-02-11 08:30:00180000
2722024-03-01 19:05:00240000
3712024-12-31 23:59:0060000
4832023-12-31 22:00:00900000
5822024-01-01 00:10:00126000
6932024-06-14 10:00:00300000
7922024-06-15 10:00:00300000
81012025-01-01 00:00:00200000

Output

listener_idtop_artistminutes_played
7Aurora Lanes4
8Bombay Bassline2.1
9Bombay Bassline5
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist; names are unique.

Table: Play

ColumnType
play_idint
listener_idint
artist_idint
played_atdatetime
ms_playedint

Primary key: play_id.

One row per play of a track, credited to the track's main artist.

Examples

Example 1

Artist

artist_idartist_name
1Aurora Lanes
2Bombay Bassline
3Coral Static

Play

play_idlistener_idartist_idplayed_atms_played
1712024-02-11 08:30:00180000
2722024-03-01 19:05:00240000
3712024-12-31 23:59:0060000
4832023-12-31 22:00:00900000
5822024-01-01 00:10:00126000
6932024-06-14 10:00:00300000
7922024-06-15 10:00:00300000
81012025-01-01 00:00:00200000

Output

listener_idtop_artistminutes_played
7Aurora Lanes4
8Bombay Bassline2.1
9Bombay Bassline5

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 = 1

Another 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 →