Explore

-- Gaps and Islands: find consecutive date ranges from sparse event data.
--
-- Given a table of user logins (one row per day a user logged in),
-- find each user's "streaks" — consecutive days of activity.
--
-- This is the classic gaps-and-islands problem. The trick: subtract
-- a row_number from each date. Consecutive dates produce the same
-- "island_id" because both the date and row_number increment by 1.

WITH daily_logins AS (
    -- Sample data: user activity log
    SELECT user_id, login_date FROM (VALUES