Longest Daily Learning Streak of Each Learner — SQL Problem
An online learning app shows each learner their best streak: the longest run of consecutive calendar days on which they completed at least one lesson.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #141
Problem statement
An online learning app shows each learner their best streak: the longest run of consecutive calendar days on which they completed at least one lesson. Several completions on one day count as one day.
For every learner with at least one completion, return learner_id, longest_streak (the number of days in their longest run) and streak_start (the first day of that run, as a date). If a learner has two runs of the same longest length, report the earlier one. Order the rows by learner_id.
Tables
Table: LessonCompletion
| Column | Type |
|---|---|
| completion_id | int |
| learner_id | int |
| lesson_id | int |
| completed_at | datetime |
Primary key: completion_id.
One row per lesson a learner finished; times are in IST.
Examples
Example 1
LessonCompletion
| completion_id | learner_id | lesson_id | completed_at |
|---|---|---|---|
| 1 | 501 | 11 | 2024-03-01 08:10:00 |
| 2 | 501 | 12 | 2024-03-02 21:40:00 |
| 3 | 501 | 13 | 2024-03-02 22:15:00 |
| 4 | 501 | 14 | 2024-03-03 07:05:00 |
| 5 | 501 | 15 | 2024-03-06 19:30:00 |
| 6 | 502 | 11 | 2024-03-01 18:00:00 |
| 7 | 502 | 12 | 2024-03-03 18:00:00 |
| 8 | 502 | 13 | 2024-03-04 09:20:00 |
| 9 | 502 | 14 | 2024-03-08 11:00:00 |
| 10 | 502 | 15 | 2024-03-09 11:30:00 |
Output
| learner_id | longest_streak | streak_start |
|---|---|---|
| 501 | 3 | 2024-03-01 |
| 502 | 2 | 2024-03-03 |
How to solve Longest Daily Learning Streak of Each Learner
This is a gaps-and-islands problem. Start by collapsing completions to distinct (learner, day) pairs with CAST(completed_at AS DATE), so two lessons on one day count once.
Then number each learner's days with ROW_NUMBER() in date order. On consecutive days the date grows by one and so does the row number, so DATEDIFF(d, some_fixed_day) - row_number is constant across a run and jumps whenever a day is skipped. That difference is a run identifier: group by learner and it, and COUNT(*) is the run's length and MIN(d) its first day.
Finally keep one run per learner — the longest, and on a tie the earliest — with another ROW_NUMBER() ordered by length descending and start ascending, which makes the choice deterministic.
The alternative finds run starts (a day whose previous day is missing) with NOT EXISTS and measures each run by counting the days that continue it without a gap. It is quadratic per learner; the window version sorts each learner's days once, so it is O(n log n).
Reference solution (MySQL)
WITH days AS (
SELECT DISTINCT learner_id, CAST(completed_at AS DATE) AS d
FROM LessonCompletion
), islands AS (
SELECT learner_id, d,
DATEDIFF(d, '2024-01-01') - ROW_NUMBER() OVER (PARTITION BY learner_id ORDER BY d) AS grp
FROM days
), runs AS (
SELECT learner_id, MIN(d) AS streak_start, COUNT(*) AS longest_streak
FROM islands
GROUP BY learner_id, grp
), ranked AS (
SELECT learner_id, longest_streak, streak_start,
ROW_NUMBER() OVER (PARTITION BY learner_id ORDER BY longest_streak DESC, streak_start) AS rn
FROM runs
)
SELECT learner_id, longest_streak, streak_start
FROM ranked
WHERE rn = 1
ORDER BY learner_idAnother way
WITH days AS (SELECT DISTINCT learner_id, CAST(completed_at AS DATE) AS d FROM LessonCompletion), starts AS (SELECT a.learner_id, a.d FROM days a WHERE NOT EXISTS (SELECT 1 FROM days b WHERE b.learner_id = a.learner_id AND b.d = DATE_SUB(a.d, INTERVAL 1 DAY))), runs AS (SELECT s.learner_id, s.d AS streak_start, (SELECT COUNT(*) FROM days x WHERE x.learner_id = s.learner_id AND x.d >= s.d AND DATEDIFF(x.d, s.d) = (SELECT COUNT(*) FROM days y WHERE y.learner_id = s.learner_id AND y.d > s.d AND y.d <= x.d)) AS longest_streak FROM starts s) SELECT r.learner_id, r.longest_streak, r.streak_start FROM runs r WHERE NOT EXISTS (SELECT 1 FROM runs q WHERE q.learner_id = r.learner_id AND (q.longest_streak > r.longest_streak OR (q.longest_streak = r.longest_streak AND q.streak_start < r.streak_start))) ORDER BY r.learner_id← Backlogs Cleared on a Later Attempt · Next-Month Retention of Learner Sign-up Cohorts →