Longest Streak Per User
Computing the maximum consecutive run length within each group.
The Question
A frequent follow-up to consecutive-day detection: "For each user, what is their longest run of consecutive active days?" Product and growth teams ask this constantly to measure engagement.
You already know how to identify each run. The new step is to find the maximum run length per user and, often, to return the dates of that best streak too. This lesson builds directly on the gaps-and-islands skeleton.
Recall the Island Builder
From the previous lesson, the per-run grouping uses login_date - ROW_NUMBER() as the island anchor. Each user can have several islands; we will compute one row per island first, then reduce to one row per user.
Keep this two-layer plan in mind: first build islands, then aggregate the islands.
WITH numbered AS (
SELECT user_id, login_date,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) AS rn
FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;