Consecutive Login Days

Prompt

Given the logins table below (one row per login, possibly several per user per day), write a query that returns every user who logged in on 3 or more consecutive calendar days.

• Each qualifying user appears once, with a consecutive_days column showing the length of that consecutive run.
• If a user has two separate runs of 3+ days, they appear once per run.
• Multiple logins on the same day count as one day for streak purposes.
• Only user_id and consecutive_days should be returned.

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

• Dedupe to one row per (user, day), then use the gaps and islands trick: login_at - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_at) is constant for consecutive days.
• In SQLite, subtract the row number with julianday(login_at) - rn or date(login_at, '-' || rn || ' days').
• Group by (user, island key) and keep HAVING COUNT(*) >= 3.

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

-- Reference: users with a run of 3+ consecutive login days (islands technique).
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
    FROM numbered
)
SELECT user_id, COUNT(*) AS consecutive_days
FROM islands
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id, grp;