advanced-window-function.sql

WITH unique_logins AS (
    -- Deduplicate logins per user per day to protect against multi-logins skewing the row count
    SELECT DISTINCT user_id, login_date 
    FROM user_logins
),
sequenced_logins AS (
    -- Subtract an incrementing row number from the login date. 
    -- For consecutive dates, this difference maps to an identical base date anchor.
    SELECT 
        user_id,
        login_date,
        login_date - INTERVAL '1 day' * ROW_NUMBER() OVER(
            PARTITION BY user_id 
            ORDER BY login_date
        ) AS streak_group
    FROM unique_logins
),
grouped_streaks AS (
    -- Aggregate by the structural group anchor to count consecutive entries
    SELECT 
        user_id,
        MIN(login_date) AS streak_start,
        MAX(login_date) AS streak_end,
        COUNT(*) AS consecutive_days
    FROM sequenced_logins
    GROUP BY user_id, streak_group
)
-- Filter out groups that do not meet our 3-day retention constraint threshold
SELECT 
    user_id,
    streak_start,
    streak_end
FROM grouped_streaks
WHERE consecutive_days >= 3
ORDER BY user_id, streak_start;