Spotify: Listeners Whose Top Genre Changed From Q1 to Q2

Hard
946.

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. 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

ColumnType
track_idint
titlevarchar
genrevarchar

Primary key: track_id.

One row per track and its primary genre.

Table: StreamLog

ColumnType
stream_idint
listener_idint
track_idint
streamed_ondate

Primary key: stream_id.

One row per stream.

Example

The rows must come back in the order shown.

Track

track_idtitlegenre
1Monsoon LettersIndie
2Chasing NeonElectronic
3Paper PlanesIndie
4Golden HourBollywood
5Quiet StormLo-fi

StreamLog

stream_idlistener_idtrack_idstreamed_on
1712024-01-05
2732024-02-11
3722024-03-31
4722024-04-01
5722024-05-20
6712024-06-02
7842024-01-09
8852024-02-14
9852024-04-03
10842024-06-30
5 more rows

Output

listener_idq1_genreq2_genre
7IndieElectronic
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: 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

ColumnType
track_idint
titlevarchar
genrevarchar

Primary key: track_id.

One row per track and its primary genre.

Table: StreamLog

ColumnType
stream_idint
listener_idint
track_idint
streamed_ondate

Primary key: stream_id.

One row per stream.

Examples

Example 1

Track

track_idtitlegenre
1Monsoon LettersIndie
2Chasing NeonElectronic
3Paper PlanesIndie
4Golden HourBollywood
5Quiet StormLo-fi

StreamLog

stream_idlistener_idtrack_idstreamed_on
1712024-01-05
2732024-02-11
3722024-03-31
4722024-04-01
5722024-05-20
6712024-06-02
7842024-01-09
8852024-02-14
9852024-04-03
10842024-06-30
11842024-07-01
12912024-03-03
13932024-05-05
141022023-12-31
151052024-04-10

Output

listener_idq1_genreq2_genre
7IndieElectronic

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_id

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