Longest Daily Learning Streak of Each Learner

Hard
141.

Longest Daily Learning Streak of Each Learner

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

ColumnType
completion_idint
learner_idint
lesson_idint
completed_atdatetime

Primary key: completion_id.

One row per lesson a learner finished; times are in IST.

Example

The rows must come back in the order shown.

LessonCompletion

completion_idlearner_idlesson_idcompleted_at
1501112024-03-01 08:10:00
2501122024-03-02 21:40:00
3501132024-03-02 22:15:00
4501142024-03-03 07:05:00
5501152024-03-06 19:30:00
6502112024-03-01 18:00:00
7502122024-03-03 18:00:00
8502132024-03-04 09:20:00
9502142024-03-08 11:00:00
10502152024-03-09 11:30:00

Output

learner_idlongest_streakstreak_start
50132024-03-01
50222024-03-03
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 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

ColumnType
completion_idint
learner_idint
lesson_idint
completed_atdatetime

Primary key: completion_id.

One row per lesson a learner finished; times are in IST.

Examples

Example 1

LessonCompletion

completion_idlearner_idlesson_idcompleted_at
1501112024-03-01 08:10:00
2501122024-03-02 21:40:00
3501132024-03-02 22:15:00
4501142024-03-03 07:05:00
5501152024-03-06 19:30:00
6502112024-03-01 18:00:00
7502122024-03-03 18:00:00
8502132024-03-04 09:20:00
9502142024-03-08 11:00:00
10502152024-03-09 11:30:00

Output

learner_idlongest_streakstreak_start
50132024-03-01
50222024-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_id

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