SQL Gaps and Islands: Finding Consecutive Runs, Streaks, and Sessions

Gaps and islands problems ask where ordered records form consecutive runs. The difficult part is not the final GROUP BY; it is defining consecutive for the business question and assigning a stable island key before aggregation.

Start by naming the entity, order column, duplicate policy, and allowed gap. A daily streak, an active-month run, a 30-minute session, and repeated device states need different markers even though all four end with grouped boundaries.

Deduplicate before finding consecutive dates

The source can contain several events for one user on one date. A daily streak counts distinct dates, so the first CTE removes duplicate user-date rows. Subtracting the row number from each date creates a constant key within a consecutive run.

Input: user_activity

WITH distinct_days AS (
  SELECT DISTINCT user_id, activity_date
  FROM user_activity
),
tagged AS (
  SELECT
    user_id,
    activity_date,
    activity_date
      - ROW_NUMBER() OVER (
          PARTITION BY user_id
          ORDER BY activity_date
        )::integer AS island_key
  FROM distinct_days
)
SELECT
  user_id,
  MIN(activity_date) AS island_start,
  MAX(activity_date) AS island_end,
  COUNT(*) AS active_days
FROM tagged
GROUP BY user_id, island_key
ORDER BY user_id, island_start;

Output

The subtraction works because both the date and row number advance by one inside a run. A missing date changes their difference and starts a new key. The SQL GROUP BY guide explains the final grain change.

Rank completed islands, not detail rows

To select the longest streak per user, first build complete island summaries. Rank those summaries by length, then use the start date as a deterministic tie breaker.

Input: user_activity

WITH distinct_days AS (
  SELECT DISTINCT user_id, activity_date
  FROM user_activity
),
tagged AS (
  SELECT
    user_id,
    activity_date,
    activity_date
      - ROW_NUMBER() OVER (
          PARTITION BY user_id
          ORDER BY activity_date
        )::integer AS island_key
  FROM distinct_days
),
islands AS (
  SELECT
    user_id,
    MIN(activity_date) AS island_start,
    MAX(activity_date) AS island_end,
    COUNT(*) AS active_days
  FROM tagged
  GROUP BY user_id, island_key
),
ranked AS (
  SELECT
    islands.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY active_days DESC, island_start
    ) AS streak_rank
  FROM islands
)
SELECT
  user_id,
  island_start,
  island_end,
  active_days
FROM ranked
WHERE streak_rank = 1
ORDER BY user_id;

Output

Use RANK or DENSE_RANK when every tied longest streak should survive. Use ROW_NUMBER with a stated tie breaker when the contract requires exactly one. The window-functions guide develops those choices.

Filter the population before assigning sequence numbers

Inactive subscription months are separators, not members of an active island. Filter them before ROW_NUMBER; otherwise they consume sequence positions and distort the active-run key.

Input: subscription_months

WITH active_months AS (
  SELECT user_id, month_start
  FROM subscription_months
  WHERE is_active
),
tagged AS (
  SELECT
    user_id,
    month_start,
    (
      EXTRACT(YEAR FROM month_start)::integer * 12
      + EXTRACT(MONTH FROM month_start)::integer
      - ROW_NUMBER() OVER (
          PARTITION BY user_id
          ORDER BY month_start
        )::integer
    ) AS island_key
  FROM active_months
)
SELECT
  user_id,
  MIN(month_start) AS island_start,
  MAX(month_start) AS island_end,
  COUNT(*) AS active_months
FROM tagged
GROUP BY user_id, island_key
ORDER BY user_id, island_start;

Output

Sessionize with a threshold and an explicit equality rule

A session starts when there is no prior event or the gap is greater than 30 minutes. With that rule, a gap exactly equal to 30 minutes remains in the current session. The running sum of start flags becomes a session number.

Input: click_events

WITH with_previous AS (
  SELECT
    event_id,
    user_id,
    event_ts,
    LAG(event_ts) OVER (
      PARTITION BY user_id
      ORDER BY event_ts, event_id
    ) AS previous_ts
  FROM click_events
),
marked AS (
  SELECT
    *,
    CASE
      WHEN previous_ts IS NULL
        OR event_ts - previous_ts > INTERVAL '30 minutes'
      THEN 1 ELSE 0
    END AS session_start
  FROM with_previous
),
numbered AS (
  SELECT
    *,
    SUM(session_start) OVER (
      PARTITION BY user_id
      ORDER BY event_ts, event_id
      ROWS UNBOUNDED PRECEDING
    ) AS session_number
  FROM marked
)
SELECT
  user_id,
  session_number,
  MIN(event_ts) AS session_start,
  MAX(event_ts) AS session_end,
  COUNT(*) AS event_count
FROM numbered
GROUP BY user_id, session_number
ORDER BY user_id, session_number;

Output

Tie the ordering to a stable key when timestamps can repeat. Without event_id, equal timestamps can be processed in an unspecified order even if the session totals happen to remain unchanged.

Collapse repeated states and expose missing ranges

For runs of equal values, mark a boundary whenever the current state differs from the previous state. PostgreSQL's IS DISTINCT FROM treats NULL as a comparable state and makes the first-row behavior explicit.

Input: device_states

WITH with_previous AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY device_id
      ORDER BY observed_at, observation_id
    ) AS sequence_number,
    LAG(state) OVER (
      PARTITION BY device_id
      ORDER BY observed_at, observation_id
    ) AS previous_state
  FROM device_states
),
numbered AS (
  SELECT
    *,
    SUM(
      CASE
        WHEN sequence_number = 1
          OR state IS DISTINCT FROM previous_state
        THEN 1 ELSE 0
      END
    ) OVER (
      PARTITION BY device_id
      ORDER BY observed_at, observation_id
      ROWS UNBOUNDED PRECEDING
    ) AS island_number
  FROM with_previous
)
SELECT
  device_id,
  island_number,
  state,
  MIN(observed_at) AS state_start,
  MAX(observed_at) AS state_end,
  COUNT(*) AS observations
FROM numbered
GROUP BY device_id, island_number, state
ORDER BY device_id, island_number;

Output

Sometimes the gaps, rather than the islands, are the output. LEAD pairs each distinct activity date with the next one; the filter keeps pairs separated by at least one missing calendar date.

Input: user_activity

WITH distinct_days AS (
  SELECT DISTINCT user_id, activity_date
  FROM user_activity
),
with_next AS (
  SELECT
    user_id,
    activity_date,
    LEAD(activity_date) OVER (
      PARTITION BY user_id
      ORDER BY activity_date
    ) AS next_activity_date
  FROM distinct_days
)
SELECT
  user_id,
  activity_date AS gap_after,
  next_activity_date AS gap_before,
  next_activity_date - activity_date - 1 AS missing_days
FROM with_next
WHERE next_activity_date - activity_date > 1
ORDER BY user_id, activity_date;

Output

Generating every missing date requires a date spine rather than LEAD. See SQL date functions for dense calendar construction and SQL practice questions for larger sequences.

FAQ

What is an island in SQL?

An island is a maximal run of rows that satisfy a stated adjacency rule. Maximal means the run cannot be extended by the next or previous ordered row without breaking that rule.

Why must I deduplicate daily events first?

Two events on one date are not two consecutive dates. Duplicates change row numbers and inflate counts unless the business question is explicitly event-based.

When should I subtract ROW_NUMBER?

Use it when the ordered value advances by a fixed unit, such as one day or one dense month index. For time gaps with a tolerance or runs of equal states, boundary flags and a running sum are clearer.

Does a 30-minute gap start a new session?

Only if the contract says so. gap > 30 minutes keeps an exact 30-minute gap in the current session; gap >= 30 minutes starts a new one. State the equality rule.

How should ties in the longest streak be handled?

Use a ranking function that matches the output contract. Keep all ties with RANK or DENSE_RANK, or choose one deterministically with ROW_NUMBER and a documented secondary order.