Spotify: Weekly Top 3 Chart With New, Re-Entry, Up and Down Labels

Hard
944.

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. 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 down n places (for example UP 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

ColumnType
track_idint
titlevarchar

Primary key: track_id.

One row per track.

Table: WeeklyStreams

ColumnType
week_startdate
track_idint
streamsint

Primary key (together): 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.

Example

The rows must come back in the order shown.

Track

track_idtitle
1Monsoon Letters
2Chasing Neon
3Paper Planes
4Golden Hour
5Quiet Storm

WeeklyStreams

week_starttrack_idstreams
2024-03-0419000
2024-03-0427000
2024-03-0435000
2024-03-0441000
2024-03-1129500
2024-03-1118000
2024-03-1146000
2024-03-1132000
2024-03-1829000
2024-03-1837000
7 more rows

Output

week_startchart_postitlemovement
2024-03-041Monsoon LettersNEW
2024-03-042Chasing NeonNEW
2024-03-043Paper PlanesNEW
2024-03-111Chasing NeonUP 1
2024-03-112Monsoon LettersDOWN 1
2024-03-113Golden HourNEW
2024-03-181Chasing NeonSAME
2024-03-182Paper PlanesRE-ENTRY
2024-03-183Golden HourSAME
2024-03-251Quiet StormNEW
2 more rows
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: 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 down n places (for example UP 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

ColumnType
track_idint
titlevarchar

Primary key: track_id.

One row per track.

Table: WeeklyStreams

ColumnType
week_startdate
track_idint
streamsint

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_idtitle
1Monsoon Letters
2Chasing Neon
3Paper Planes
4Golden Hour
5Quiet Storm

WeeklyStreams

week_starttrack_idstreams
2024-03-0419000
2024-03-0427000
2024-03-0435000
2024-03-0441000
2024-03-1129500
2024-03-1118000
2024-03-1146000
2024-03-1132000
2024-03-1829000
2024-03-1837000
2024-03-1847000
2024-03-1813000
2024-03-185500
2024-03-25512000
2024-03-2518000
2024-03-2528000
2024-03-253100

Output

week_startchart_postitlemovement
2024-03-041Monsoon LettersNEW
2024-03-042Chasing NeonNEW
2024-03-043Paper PlanesNEW
2024-03-111Chasing NeonUP 1
2024-03-112Monsoon LettersDOWN 1
2024-03-113Golden HourNEW
2024-03-181Chasing NeonSAME
2024-03-182Paper PlanesRE-ENTRY
2024-03-183Golden HourSAME
2024-03-251Quiet StormNEW
2024-03-252Monsoon LettersRE-ENTRY
2024-03-253Chasing NeonDOWN 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.pos

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