Spotify: Listeners Whose Top Genre Changed From Q1 to Q2
The taste-profile team tracks listeners whose taste is shifting, because their recommendations need refreshing.
- Difficulty: Hard
- Topics: Window Functions, Dates, Joins
- Dialect: MySQL
- Problem: #946
Problem statement
The taste-profile team tracks listeners whose taste is shifting, because their recommendations need refreshing. A listener's top genre in a quarter is the genre of the tracks they streamed most often that quarter, counted in streams; a tie goes to the genre that comes first alphabetically. Q1 is January–March 2024 and Q2 is April–June 2024.
Return every listener who streamed in both quarters and whose top genre in Q2 differs from Q1, with the columns listener_id, q1_genre and q2_genre. Streams outside the first half of 2024 are ignored. Order by listener_id.
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 |
| genre | varchar |
Primary key: track_id.
One row per track and its primary genre.
Table: StreamLog
| Column | Type |
|---|---|
| stream_id | int |
| listener_id | int |
| track_id | int |
| streamed_on | date |
Primary key: stream_id.
One row per stream.
Examples
Example 1
Track
| track_id | title | genre |
|---|---|---|
| 1 | Monsoon Letters | Indie |
| 2 | Chasing Neon | Electronic |
| 3 | Paper Planes | Indie |
| 4 | Golden Hour | Bollywood |
| 5 | Quiet Storm | Lo-fi |
StreamLog
| stream_id | listener_id | track_id | streamed_on |
|---|---|---|---|
| 1 | 7 | 1 | 2024-01-05 |
| 2 | 7 | 3 | 2024-02-11 |
| 3 | 7 | 2 | 2024-03-31 |
| 4 | 7 | 2 | 2024-04-01 |
| 5 | 7 | 2 | 2024-05-20 |
| 6 | 7 | 1 | 2024-06-02 |
| 7 | 8 | 4 | 2024-01-09 |
| 8 | 8 | 5 | 2024-02-14 |
| 9 | 8 | 5 | 2024-04-03 |
| 10 | 8 | 4 | 2024-06-30 |
| 11 | 8 | 4 | 2024-07-01 |
| 12 | 9 | 1 | 2024-03-03 |
| 13 | 9 | 3 | 2024-05-05 |
| 14 | 10 | 2 | 2023-12-31 |
| 15 | 10 | 5 | 2024-04-10 |
Output
| listener_id | q1_genre | q2_genre |
|---|---|---|
| 7 | Indie | Electronic |
How to solve Spotify: Listeners Whose Top Genre Changed From Q1 to Q2
Three steps. First, count: label each stream with its quarter (before 1 April is Q1, otherwise Q2, after limiting to the first half of 2024) and count streams per listener, quarter and genre. QUARTER(streamed_on) builds the same label. The boundaries are exact: 31 March is Q1, 1 April is Q2, and 31 December 2023 and 1 July 2024 are out.
Second, pick: ROW_NUMBER() per listener and quarter, ordered by streams descending then genre ascending, gives exactly one top genre even when genres tie. Listener 8 streamed Bollywood once and Lo-fi once in Q1, so Bollywood wins alphabetically; in Q2 Lo-fi and Bollywood again tie once each (the July stream is ignored) — Bollywood again, so no change is reported. Listener 7 moves from Indie (2 of 3) to Electronic (2 of 3).
Third, compare: an inner self-join of the Q1 picks to the Q2 picks keeps only listeners present in both quarters (listener 10's only other stream is from 2023), and a.genre <> b.genre keeps the changers. The alternative picks the top genre with NOT EXISTS a better one. The cost is one aggregation of the streams plus a sort per listener-quarter.
Reference solution (MySQL)
WITH per_quarter AS (
SELECT s.listener_id,
CASE WHEN s.streamed_on < '2024-04-01' THEN 1 ELSE 2 END AS qtr,
t.genre, COUNT(*) AS streams
FROM StreamLog s
JOIN Track t ON t.track_id = s.track_id
WHERE s.streamed_on BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY s.listener_id, CASE WHEN s.streamed_on < '2024-04-01' THEN 1 ELSE 2 END, t.genre
), ranked AS (
SELECT listener_id, qtr, genre,
ROW_NUMBER() OVER (PARTITION BY listener_id, qtr ORDER BY streams DESC, genre) AS rn
FROM per_quarter
)
SELECT a.listener_id, a.genre AS q1_genre, b.genre AS q2_genre
FROM ranked a
JOIN ranked b ON b.listener_id = a.listener_id AND b.qtr = 2 AND b.rn = 1
WHERE a.qtr = 1 AND a.rn = 1 AND a.genre <> b.genre
ORDER BY a.listener_idAnother way
WITH per_quarter AS (SELECT s.listener_id, QUARTER(s.streamed_on) AS qtr, t.genre, COUNT(*) AS streams
FROM StreamLog s JOIN Track t ON t.track_id = s.track_id
WHERE YEAR(s.streamed_on) = 2024 AND QUARTER(s.streamed_on) IN (1, 2)
GROUP BY s.listener_id, QUARTER(s.streamed_on), t.genre),
tops AS (SELECT p.listener_id, p.qtr, p.genre FROM per_quarter p
WHERE NOT EXISTS (SELECT 1 FROM per_quarter o WHERE o.listener_id = p.listener_id AND o.qtr = p.qtr
AND (o.streams > p.streams OR (o.streams = p.streams AND o.genre < p.genre))))
SELECT q1.listener_id, q1.genre AS q1_genre, q2.genre AS q2_genre
FROM tops q1 JOIN tops q2 ON q2.listener_id = q1.listener_id AND q2.qtr = 2
WHERE q1.qtr = 1 AND q1.genre <> q2.genre
ORDER BY q1.listener_id← Spotify: Pro-Rata Royalty Pool Payouts for June 2024 · Spotify: Playlist Pairs Sharing Half Their Available Tracks →