Gaps-and-islands SQL for compressing event streaks

Given a table of (ts, user_id, state) you want to compress consecutive rows with the same state into a single row with start_ts and end_ts. “On-call rotation,” “feature flag A/B assignment,” “subscription tier over time.” This is the classic gaps-and-islands problem, and the modern solution with window functions is short once you have seen it once.

The trick: compute two row numbers, one partitioned by user alone and one partitioned by user plus state. The difference is constant within a contiguous run, so grouping by that difference gives you the islands.

WITH numbered AS (
    SELECT
        user_id,
        state,
        ts,
        row_number() OVER (PARTITION BY user_id          ORDER BY ts) AS rn_all,
        row_number() OVER (PARTITION BY user_id, state   ORDER BY ts) AS rn_state
    FROM events
),
runs AS (
    SELECT
        user_id,
        state,
        rn_all - rn_state AS grp,
        min(ts) AS start_ts,
        max(ts) AS end_ts,
        count(*) AS n_events
    FROM numbered
    GROUP BY user_id, state, rn_all - rn_state
)
SELECT user_id, state, start_ts, end_ts, n_events
FROM runs
ORDER BY user_id, start_ts;

Caveat: this assumes “consecutive” means adjacent rows, not “within some time window.” If you need the latter, you need lag() with a timestamp difference and a running sum of gap markers instead.