SQL: users who logged in 3 days in a row

Why the usual answer is wrong

SELECT user_id
FROM logins
GROUP BY user_id
HAVING COUNT(DISTINCT login_date) >= 3;

User 2 logged in on 3 days, but 1, 3 and 5 March are not in a row. Counting days says nothing about whether they follow each other.

The answer

WITH days AS (
  SELECT DISTINCT user_id, login_date FROM logins
),
groups AS (
  SELECT user_id,
    julianday(login_date)
      - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
  FROM days
)
SELECT DISTINCT user_id
FROM groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Remove repeat logins on the same day first. Then, for days in a row, the date and the row number both go up by 1, so date minus row number stays the same: that value labels each streak.

Group by user and streak label and keep the streaks of 3 or more days. In PostgreSQL, write login_date - ROW_NUMBER() … :: int on a date column.

SELECT DISTINCT a.user_id
FROM logins a
JOIN logins b ON b.user_id = a.user_id AND b.login_date = date(a.login_date, '+1 day')
JOIN logins c ON c.user_id = a.user_id AND c.login_date = date(a.login_date, '+2 day');