Longest Daily Usage Streak per User — SQL Problem
The app shows each user their best streak: the longest run of consecutive calendar days on which they used the product at least once.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #419
Problem statement
The app shows each user their best streak: the longest run of consecutive calendar days on which they used the product at least once. Several uses on one day are one day, and a streak may cross a month boundary.
Return user_id, longest_streak (in days) and streak_start (the first day of that streak) for every user with usage. If a user has two streaks of the same longest length, report the earliest one. Order by user_id.
Tables
Table: usage_log
| Column | Type |
|---|---|
| log_id | int |
| user_id | int |
| used_at | datetime |
Primary key: log_id.
One row per use of the app.
Examples
Example 1
usage_log
| log_id | user_id | used_at |
|---|---|---|
| 1 | 1 | 2024-06-01 09:00:00 |
| 2 | 1 | 2024-06-02 08:30:00 |
| 3 | 1 | 2024-06-02 21:15:00 |
| 4 | 1 | 2024-06-03 07:45:00 |
| 5 | 1 | 2024-06-05 10:00:00 |
| 6 | 1 | 2024-06-06 10:00:00 |
| 7 | 1 | 2024-06-07 23:59:00 |
| 8 | 2 | 2024-06-10 12:00:00 |
| 9 | 3 | 2024-06-28 18:00:00 |
| 10 | 3 | 2024-06-29 18:00:00 |
| 11 | 3 | 2024-06-30 18:00:00 |
| 12 | 3 | 2024-07-01 00:05:00 |
| 13 | 3 | 2024-07-03 18:00:00 |
Output
| user_id | longest_streak | streak_start |
|---|---|---|
| 1 | 3 | 2024-06-01 |
| 2 | 1 | 2024-06-10 |
| 3 | 4 | 2024-06-28 |
How to solve Longest Daily Usage Streak per User
A classic gaps and islands problem. Deduplicate to distinct (user, day) pairs — two uses on 2 June are one day — then number each user's days in order with ROW_NUMBER(). Inside a run of consecutive days both the date and the row number rise by exactly one, so DATEDIFF(day, '2000-01-01') - row_number is the same for every day of the run and changes at every gap. That value is the island's key; it is computed on integer day numbers rather than by subtracting an interval from a date, which keeps it simple in every engine.
Grouping by (user, key) gives each streak's first day and length. Because the key comes from day arithmetic, a streak crossing a month boundary — 28 June to 1 July for user 3 — stays one island.
Choosing one streak per user with ties is a top-1-per-group: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY len DESC, streak_start) makes the longest come first and the earliest win a tie (user 1 has two three-day streaks, and 1 June wins).
The alternative marks island starts with LAG — a day whose previous day is not exactly one day earlier — numbers islands with a running sum, and keeps the streak no other streak beats via NOT EXISTS. Both are a sort per user.
Reference solution (MySQL)
WITH days AS (
SELECT DISTINCT user_id, DATE(used_at) AS day FROM usage_log
), islands AS (
SELECT user_id, day,
DATEDIFF(day, '2000-01-01') - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS grp
FROM days
), streaks AS (
SELECT user_id, MIN(day) AS streak_start, COUNT(*) AS len
FROM islands
GROUP BY user_id, grp
), ranked AS (
SELECT user_id, streak_start, len,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY len DESC, streak_start) AS rn
FROM streaks
)
SELECT user_id, len AS longest_streak, streak_start
FROM ranked
WHERE rn = 1
ORDER BY user_idAnother way
WITH days AS (
SELECT DISTINCT user_id, DATE(used_at) AS day FROM usage_log
), marked AS (
SELECT user_id, day,
CASE WHEN DATEDIFF(day, LAG(day) OVER (PARTITION BY user_id ORDER BY day)) = 1 THEN 0 ELSE 1 END AS is_start
FROM days
), numbered AS (
SELECT user_id, day, SUM(is_start) OVER (PARTITION BY user_id ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
FROM marked
), streaks AS (
SELECT user_id, island, MIN(day) AS streak_start, COUNT(*) AS len FROM numbered GROUP BY user_id, island
)
SELECT s.user_id, s.len AS longest_streak, s.streak_start
FROM streaks s
WHERE NOT EXISTS (SELECT 1 FROM streaks t WHERE t.user_id = s.user_id
AND (t.len > s.len OR (t.len = s.len AND t.streak_start < s.streak_start)))
ORDER BY s.user_id← Monthly Signup Cohort Retention · Month-End MRR From Subscription Periods →