Longest Daily Listening Streak per Listener

Hard
492.

Longest Daily Listening Streak per Listener

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

ColumnType
play_idint
listener_idint
played_atdatetime

Primary key: play_id.

One row per play.

Example

The rows must come back in the order shown.

Play

play_idlistener_idplayed_at
1312024-12-01 08:00:00
2312024-12-02 22:15:00
3312024-12-02 23:40:00
4312024-12-03 07:05:00
5312024-12-06 19:00:00
6322024-12-01 10:00:00
7322024-12-02 10:00:00
8322024-12-05 10:00:00
9322024-12-06 10:00:00
10332024-12-04 23:59:00

Output

listener_idlongest_streakstreak_start
3132024-12-01
3222024-12-01
3312024-12-04
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.

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

ColumnType
play_idint
listener_idint
played_atdatetime

Primary key: play_id.

One row per play.

Examples

Example 1

Play

play_idlistener_idplayed_at
1312024-12-01 08:00:00
2312024-12-02 22:15:00
3312024-12-02 23:40:00
4312024-12-03 07:05:00
5312024-12-06 19:00:00
6322024-12-01 10:00:00
7322024-12-02 10:00:00
8322024-12-05 10:00:00
9322024-12-06 10:00:00
10332024-12-04 23:59:00

Output

listener_idlongest_streakstreak_start
3132024-12-01
3222024-12-01
3312024-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_id

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