Longest Daily Posting Streak per Creator — SQL Problem
Creators earn a badge for posting every day. A streak is a run of consecutive calendar days on each of which the creator posted at least once (several posts…
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #443
Problem statement
Creators earn a badge for posting every day. A streak is a run of consecutive calendar days on each of which the creator posted at least once (several posts on one day count as one day; the date is the date part of posted_at).
For every creator with at least one post, return the columns creator_id, longest_streak (its length in days) and streak_start (the first day of that streak; if two streaks tie for longest, the earlier one), ordered by creator_id.
Tables
Table: posts
| Column | Type |
|---|---|
| post_id | int |
| creator_id | int |
| posted_at | datetime |
Primary key: post_id.
One row per post, with the time it went live (IST).
Examples
Example 1
posts
| post_id | creator_id | posted_at |
|---|---|---|
| 1 | 1 | 2025-08-01 09:00:00 |
| 2 | 1 | 2025-08-02 21:30:00 |
| 3 | 1 | 2025-08-02 23:10:00 |
| 4 | 1 | 2025-08-03 07:45:00 |
| 5 | 1 | 2025-08-05 12:00:00 |
| 6 | 1 | 2025-08-06 12:00:00 |
| 7 | 2 | 2025-08-10 18:00:00 |
| 8 | 2 | 2025-08-12 18:00:00 |
| 9 | 3 | 2025-07-30 22:00:00 |
| 10 | 3 | 2025-07-31 23:59:59 |
| 11 | 3 | 2025-08-01 00:00:01 |
Output
| creator_id | longest_streak | streak_start |
|---|---|---|
| 1 | 3 | 2025-08-01 |
| 2 | 1 | 2025-08-10 |
| 3 | 3 | 2025-07-30 |
How to solve Longest Daily Posting Streak per Creator
This is gaps and islands. Start with the distinct posting days per creator (DATE(posted_at)), so that two posts on one day count once — and note that 23:59:59 on 31 July and 00:00:01 on 1 August are two different days.
Number each creator's days with ROW_NUMBER() in date order. Along a run of consecutive days both the date and the row number go up by one per row, so DATE_SUB(d, INTERVAL rn DAY) is constant inside a run and jumps when a day is skipped. Grouping by that key gives every streak with MIN(d) as its start and COUNT(*) as its length.
The last step is top-1 per creator with a tie rule: ROW_NUMBER() ordered by len DESC, start_day picks the longest and, among equals, the earliest. The alternative finds islands differently — a day starts a new island unless it is exactly one day after the previous one (LAG + DATEDIFF), and a running SUM of those flags numbers the islands — and selects the longest with a correlated MAX, taking the earliest start with MIN. Both sort each creator's days once.
Reference solution (MySQL)
WITH days AS (
SELECT DISTINCT creator_id, DATE(posted_at) AS d FROM posts
), numbered AS (
SELECT creator_id, d, ROW_NUMBER() OVER (PARTITION BY creator_id ORDER BY d) AS rn FROM days
), streaks AS (
SELECT creator_id, MIN(d) AS start_day, COUNT(*) AS len
FROM (SELECT creator_id, d, DATE_SUB(d, INTERVAL rn DAY) AS grp FROM numbered) x
GROUP BY creator_id, grp
), best AS (
SELECT creator_id, start_day, len,
ROW_NUMBER() OVER (PARTITION BY creator_id ORDER BY len DESC, start_day) AS k
FROM streaks
)
SELECT creator_id, len AS longest_streak, start_day AS streak_start
FROM best WHERE k = 1
ORDER BY creator_idAnother way
WITH days AS (SELECT DISTINCT creator_id, DATE(posted_at) AS d FROM posts),
flagged AS (
SELECT creator_id, d,
CASE WHEN DATEDIFF(d, LAG(d) OVER (PARTITION BY creator_id ORDER BY d)) = 1 THEN 0 ELSE 1 END AS is_new
FROM days
), islands AS (
SELECT creator_id, d,
SUM(is_new) OVER (PARTITION BY creator_id ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
FROM flagged
), streaks AS (
SELECT creator_id, island, MIN(d) AS start_day, COUNT(*) AS len FROM islands GROUP BY creator_id, island
)
SELECT s.creator_id, s.len AS longest_streak, MIN(s.start_day) AS streak_start
FROM streaks s
WHERE s.len = (SELECT MAX(t.len) FROM streaks t WHERE t.creator_id = s.creator_id)
GROUP BY s.creator_id, s.len
ORDER BY s.creator_id← Friend Suggestions From Two or More Mutual Friends · First-Week Retention by Sign-Up Month →