Longest Login Streak

Prompt

Given the logins table below (one row per login, possibly several per user per day), write a query that returns, for each user, the length of their longest run of consecutive login days.

• Output columns: user_id and longest_streak, ordered by user_id.
• A user with a single login has a streak of 1.
• Multiple logins on the same day count as one day.
• If two runs are tied for the longest, the length is still reported once.

Example

Expected result:

Constraints

• logins has one row per login; a user can log in several times per day.
• logins.login_at is a DATE and is not NULL.

Tips

• Same islands technique as "Consecutive Login Days", but finish with MAX(streak_len) per user.
• Dedupe to one row per (user, day) first, or the streak math silently breaks.

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: longest consecutive-day run per user.
WITH deduped AS (
    SELECT DISTINCT user_id, login_at
    FROM logins
),
numbered AS (
    SELECT user_id, login_at,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_at) AS rn
    FROM deduped
),
islands AS (
    SELECT user_id,
           date(login_at, '-' || rn || ' days') AS grp,
           COUNT(*) AS streak_len
    FROM numbered
    GROUP BY user_id, date(login_at, '-' || rn || ' days')
)
SELECT user_id, MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;