Spotify: Weekly Top 3 Chart With New, Re-Entry, Up and Down Labels
Spotify's charts team publishes a weekly chart, and every entry carries a movement label next to its position.
- Difficulty: Hard
- Topics: Window Functions, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #944
Problem statement
Spotify's charts team publishes a weekly chart, and every entry carries a movement label next to its position. A week's chart is its top 3 tracks by streams; when two tracks have the same streams, the lower track_id ranks higher. Positions are 1, 2 and 3.
Label each chart entry by comparing it with the chart of the week exactly 7 days earlier:
NEW— the track has never been on any earlier week's chart;RE-ENTRY— it was on an earlier chart, but not on the previous week's;UP n/DOWN n— it was on the previous week's chart, and has moved up or downnplaces (for exampleUP 1,DOWN 2);SAME— it holds the position it had the previous week.
Return every chart entry of every week, with the columns week_start, chart_pos, title and movement. Order by week_start, then chart_pos.
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: Track
| Column | Type |
|---|---|
| track_id | int |
| title | varchar |
Primary key: track_id.
One row per track.
Table: WeeklyStreams
| Column | Type |
|---|---|
| week_start | date |
| track_id | int |
| streams | int |
Primary key: week_start, track_id.
A track's total streams in the chart week starting on week_start (always a Monday). A track with no streams that week has no row.
Examples
Example 1
Track
| track_id | title |
|---|---|
| 1 | Monsoon Letters |
| 2 | Chasing Neon |
| 3 | Paper Planes |
| 4 | Golden Hour |
| 5 | Quiet Storm |
WeeklyStreams
| week_start | track_id | streams |
|---|---|---|
| 2024-03-04 | 1 | 9000 |
| 2024-03-04 | 2 | 7000 |
| 2024-03-04 | 3 | 5000 |
| 2024-03-04 | 4 | 1000 |
| 2024-03-11 | 2 | 9500 |
| 2024-03-11 | 1 | 8000 |
| 2024-03-11 | 4 | 6000 |
| 2024-03-11 | 3 | 2000 |
| 2024-03-18 | 2 | 9000 |
| 2024-03-18 | 3 | 7000 |
| 2024-03-18 | 4 | 7000 |
| 2024-03-18 | 1 | 3000 |
| 2024-03-18 | 5 | 500 |
| 2024-03-25 | 5 | 12000 |
| 2024-03-25 | 1 | 8000 |
| 2024-03-25 | 2 | 8000 |
| 2024-03-25 | 3 | 100 |
Output
| week_start | chart_pos | title | movement |
|---|---|---|---|
| 2024-03-04 | 1 | Monsoon Letters | NEW |
| 2024-03-04 | 2 | Chasing Neon | NEW |
| 2024-03-04 | 3 | Paper Planes | NEW |
| 2024-03-11 | 1 | Chasing Neon | UP 1 |
| 2024-03-11 | 2 | Monsoon Letters | DOWN 1 |
| 2024-03-11 | 3 | Golden Hour | NEW |
| 2024-03-18 | 1 | Chasing Neon | SAME |
| 2024-03-18 | 2 | Paper Planes | RE-ENTRY |
| 2024-03-18 | 3 | Golden Hour | SAME |
| 2024-03-25 | 1 | Quiet Storm | NEW |
| 2024-03-25 | 2 | Monsoon Letters | RE-ENTRY |
| 2024-03-25 | 3 | Chasing Neon | DOWN 2 |
How to solve Spotify: Weekly Top 3 Chart With New, Re-Entry, Up and Down Labels
Build the chart first. ROW_NUMBER() OVER (PARTITION BY week_start ORDER BY streams DESC, track_id) numbers each week's tracks, and the tie-break inside the window makes the positions unique — on 18 March Paper Planes and Golden Hour both have 7,000 streams, and the lower id takes position 2. Keeping pos <= 3 gives the chart, which is the only thing compared from here on: a track that ranked 4th last week was not on last week's chart.
Then label each entry. A LEFT JOIN of the chart to itself on the same track and week_start exactly seven days earlier finds last week's position. If it exists, compare: a smaller number now is a climb (UP n, with n = previous − current), a larger one a fall, equal is SAME. If it does not, the entry is either a debut or a comeback, and only the full history can tell: EXISTS an earlier chart row of the track means RE-ENTRY, otherwise NEW. Monsoon Letters charts in weeks 1 and 2, drops out in week 3 and comes back in week 4 as RE-ENTRY; Quiet Storm's first chart is week 4, so it is NEW even though it had streams in week 3.
Two MySQL details: ROW_NUMBER() is unsigned, so each CASE branch subtracts only in the positive direction, and CONCAT('UP ', n) turns the number into text. The alternative walks each track's own chart history with LAG — its previous chart week and position — and calls a gap of more than seven days a re-entry; it uses RANK(), which equals ROW_NUMBER() here because the tie-break makes every position unique. Both sort the weekly rows once per window.
Reference solution (MySQL)
WITH chart AS (
SELECT week_start, track_id,
ROW_NUMBER() OVER (PARTITION BY week_start ORDER BY streams DESC, track_id) AS pos
FROM WeeklyStreams
), top3 AS (
SELECT week_start, track_id, pos FROM chart WHERE pos <= 3
)
SELECT c.week_start, c.pos AS chart_pos, t.title,
CASE
WHEN p.pos IS NULL THEN
CASE WHEN EXISTS (SELECT 1 FROM top3 e WHERE e.track_id = c.track_id AND e.week_start < c.week_start)
THEN 'RE-ENTRY' ELSE 'NEW' END
WHEN p.pos > c.pos THEN CONCAT('UP ', p.pos - c.pos)
WHEN p.pos < c.pos THEN CONCAT('DOWN ', c.pos - p.pos)
ELSE 'SAME'
END AS movement
FROM top3 c
JOIN Track t ON t.track_id = c.track_id
LEFT JOIN top3 p ON p.track_id = c.track_id AND p.week_start = DATE_SUB(c.week_start, INTERVAL 7 DAY)
ORDER BY c.week_start, c.posAnother way
WITH top3 AS (SELECT week_start, track_id, pos FROM (SELECT week_start, track_id,
RANK() OVER (PARTITION BY week_start ORDER BY streams DESC, track_id) AS pos FROM WeeklyStreams) r WHERE pos <= 3),
history AS (SELECT week_start, track_id, pos,
LAG(week_start) OVER (PARTITION BY track_id ORDER BY week_start) AS prev_week,
LAG(pos) OVER (PARTITION BY track_id ORDER BY week_start) AS prev_pos
FROM top3)
SELECT h.week_start, h.pos AS chart_pos, t.title,
CASE WHEN h.prev_week IS NULL THEN 'NEW'
WHEN DATEDIFF(h.week_start, h.prev_week) > 7 THEN 'RE-ENTRY'
WHEN h.prev_pos > h.pos THEN CONCAT('UP ', h.prev_pos - h.pos)
WHEN h.prev_pos < h.pos THEN CONCAT('DOWN ', h.pos - h.prev_pos)
ELSE 'SAME' END AS movement
FROM history h JOIN Track t ON t.track_id = h.track_id
ORDER BY h.week_start, h.pos← Spotify: Family Plans That Ever Had Over Six Members at Once · Spotify: Pro-Rata Royalty Pool Payouts for June 2024 →