Find the Longest Streak Per User

Problem

Schema: Activity(user_id INT, activity_date DATE). Each (user_id, activity_date) is unique. Return each user_id and the length of their longest consecutive-day streak.

Constraints

• Dates can be sparse; gaps end a streak.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

WITH g AS (
  SELECT user_id, activity_date,
         julianday(activity_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS streak_key
  FROM Activity
),
lens AS (
  SELECT user_id, COUNT(*) AS streak_len
  FROM g
  GROUP BY user_id, streak_key
)
SELECT user_id, MAX(streak_len) AS longest_streak
FROM lens
GROUP BY user_id
ORDER BY user_id;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.