Longest Daily Listening Streak per Listener — SQL Problem
The music app's year-in-review card shows each listener's longest streak: the most consecutive calendar days with at least one play.
- Difficulty: Hard
- Topics: Window Functions, Dates, Subqueries
- Dialect: MySQL
- Problem: #492
Problem statement
The music app's year-in-review card shows each listener's longest streak: the most consecutive calendar days with at least one play. Several plays on one day count once.
Return one row per listener who has any play, with the columns listener_id, longest_streak (in days) and streak_start (the first day of that streak; if two streaks are equally long, the earliest one). Order by listener_id.
Tables
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| listener_id | int |
| played_at | datetime |
Primary key: play_id.
One row per play.
Examples
Example 1
Play
| play_id | listener_id | played_at |
|---|---|---|
| 1 | 31 | 2024-12-01 08:00:00 |
| 2 | 31 | 2024-12-02 22:15:00 |
| 3 | 31 | 2024-12-02 23:40:00 |
| 4 | 31 | 2024-12-03 07:05:00 |
| 5 | 31 | 2024-12-06 19:00:00 |
| 6 | 32 | 2024-12-01 10:00:00 |
| 7 | 32 | 2024-12-02 10:00:00 |
| 8 | 32 | 2024-12-05 10:00:00 |
| 9 | 32 | 2024-12-06 10:00:00 |
| 10 | 33 | 2024-12-04 23:59:00 |
Output
| listener_id | longest_streak | streak_start |
|---|---|---|
| 31 | 3 | 2024-12-01 |
| 32 | 2 | 2024-12-01 |
| 33 | 1 | 2024-12-04 |
How to solve Longest Daily Listening Streak per Listener
The classic gaps-and-islands trick. Once plays are reduced to distinct (listener, day) pairs, number each listener's days with ROW_NUMBER() in date order. Along a run of consecutive days the date goes up by one and so does the row number, so day_number − row_number is the same for every day of the run and changes after any gap. Here the day number is DATEDIFF(d, '2000-01-01'), which keeps the subtraction in plain integers.
Grouping by (listener, that difference) yields one row per streak: COUNT(*) days long, starting at MIN(d). Then a second ROW_NUMBER() per listener ordered by length descending and start ascending picks the longest, with the earliest start winning a tie — listener 32 has two two-day streaks and the one from 1 December is reported. The DISTINCT step matters: two plays on one day would otherwise take two row numbers and break the arithmetic.
Without windows, a streak starts on a day whose previous day has no play (NOT EXISTS), and its length counts the days up to the next start. That form is quadratic; the window plan is two sorts per listener.
Reference solution (MySQL)
WITH days AS (
SELECT DISTINCT listener_id, CAST(played_at AS DATE) AS d FROM Play
), islands AS (
SELECT listener_id, d,
DATEDIFF(d, '2000-01-01') - ROW_NUMBER() OVER (PARTITION BY listener_id ORDER BY d) AS grp
FROM days
), streaks AS (
SELECT listener_id, MIN(d) AS streak_start, COUNT(*) AS streak_len
FROM islands
GROUP BY listener_id, grp
), ranked AS (
SELECT listener_id, streak_start, streak_len,
ROW_NUMBER() OVER (PARTITION BY listener_id ORDER BY streak_len DESC, streak_start) AS rn
FROM streaks
)
SELECT listener_id, streak_len AS longest_streak, streak_start
FROM ranked
WHERE rn = 1
ORDER BY listener_idAnother way
WITH days AS (SELECT DISTINCT listener_id, LEFT(played_at, 10) AS d FROM Play),
starts AS (
SELECT a.listener_id, a.d FROM days a
WHERE NOT EXISTS (SELECT 1 FROM days b WHERE b.listener_id = a.listener_id AND b.d = DATE_SUB(a.d, INTERVAL 1 DAY))
),
streaks AS (
SELECT s.listener_id, s.d AS streak_start,
(SELECT COUNT(*) FROM days x WHERE x.listener_id = s.listener_id AND x.d >= s.d
AND x.d < COALESCE((SELECT MIN(e.d) FROM starts e WHERE e.listener_id = s.listener_id AND e.d > s.d), '9999-12-31')) AS streak_len
FROM starts s
)
SELECT listener_id, streak_len AS longest_streak, MIN(streak_start) AS streak_start
FROM streaks t
WHERE streak_len = (SELECT MAX(u.streak_len) FROM streaks u WHERE u.listener_id = t.listener_id)
GROUP BY listener_id, streak_len
ORDER BY listener_id← Binge-Watch Runs of Three or More Episodes · Next-Month Viewing Retention of Streaming Sign-Up Cohorts →