sql

🔷 SQL: The Consecutive Days Pattern (Tricky!)

"Find users who logged in 3+ consecutive days."

WITH numbered AS (
  SELECT user_id, login_date,
    login_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS grp
  FROM (SELECT DISTINCT user_id, login_date FROM logins) t
)
SELECT user_id, COUNT(*) AS streak
FROM numbered
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Why this works: For consecutive dates, subtracting an incrementing row number produces the SAME value (the "group anchor"). Non-consecutive dates produce different values, splitting into separate groups.

Example: dates 1,2,3,5,6 with row_numbers 1,2,3,4,5 → differences: 0,0,0,1,1 → two groups.