Gaps and Islands: Detect 3+ Day Active Login Streaks in SQL

Question

Given `user_logins` (user_id, login_date), write a SQL query to find all users who logged in for at least 3 consecutive calendar days.

Accepted Answer

Optimal Solution:
WITH ranked_dates AS (
  SELECT DISTINCT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM user_logins
),
streak_groups AS (
  SELECT 
    user_id,
    login_date,
    login_date - (rn * INTERVAL '1 day') AS grp
  FROM ranked_dates
)
SELECT 
  user_id,
  MIN(login_date) AS streak_start,
  COUNT(*) AS streak_length
FROM streak_groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id;

Technical Explanation:
The difference `login_date - (rn * 1 day)` is constant for consecutive days. This creates a fixed group identifier `grp` for each unbroken sequence (island).

user_id	login_date
1	2026-03-01
1	2026-03-02
1	2026-03-03
1	2026-03-05
2	2026-03-01
2	2026-03-03