Longest Daily Posting Streak per Creator

Hard
443.

Longest Daily Posting Streak per Creator

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

ColumnType
post_idint
creator_idint
posted_atdatetime

Primary key: post_id.

One row per post, with the time it went live (IST).

Example

The rows must come back in the order shown.

posts

post_idcreator_idposted_at
112025-08-01 09:00:00
212025-08-02 21:30:00
312025-08-02 23:10:00
412025-08-03 07:45:00
512025-08-05 12:00:00
612025-08-06 12:00:00
722025-08-10 18:00:00
822025-08-12 18:00:00
932025-07-30 22:00:00
1032025-07-31 23:59:59
1 more row

Output

creator_idlongest_streakstreak_start
132025-08-01
212025-08-10
332025-07-30
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 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

ColumnType
post_idint
creator_idint
posted_atdatetime

Primary key: post_id.

One row per post, with the time it went live (IST).

Examples

Example 1

posts

post_idcreator_idposted_at
112025-08-01 09:00:00
212025-08-02 21:30:00
312025-08-02 23:10:00
412025-08-03 07:45:00
512025-08-05 12:00:00
612025-08-06 12:00:00
722025-08-10 18:00:00
822025-08-12 18:00:00
932025-07-30 22:00:00
1032025-07-31 23:59:59
1132025-08-01 00:00:01

Output

creator_idlongest_streakstreak_start
132025-08-01
212025-08-10
332025-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_id

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