Why Gap-and-Island Matters

Every time I need to group consecutive rows — think user sessions, sensor streaks, or order sequences — I reach for the same pattern: a CTE that tags each row with a group identifier using window functions. It’s concise, set‑based, and runs fast on modern engines.

The Real‑World Scenario

Imagine a table events that logs a user’s activity timestamps. The business wants to know how many distinct sessions each user had, where a session ends after 30 minutes of inactivity. Writing a procedural loop would be painful and slow; a single set‑based query does the job.

The Solution in One Query

WITH ordered AS (
    SELECT
        user_id,
        event_ts,
        LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) AS prev_ts
    FROM events
),
flagged AS (
    SELECT
        user_id,
        event_ts,
        CASE
            WHEN prev_ts IS NULL THEN 1
            WHEN event_ts - prev_ts > INTERVAL '30 minutes' THEN 1
            ELSE 0
        END AS new_session_flag
    FROM ordered
),
sessionized AS (
    SELECT
        user_id,
        event_ts,
        SUM(new_session_flag) OVER (PARTITION BY user_id ORDER BY event_ts
                                    ROWS UNBOUNDED PRECEDING) AS session_id
    FROM flagged
)
SELECT
    user_id,
    session_id,
    MIN(event_ts) AS session_start,
    MAX(event_ts) AS session_end,
    COUNT(*) AS event_count
FROM sessionized
GROUP BY user_id, session_id
ORDER BY user_id, session_start;

Breaking Down the Steps

  1. ordered – pulls the previous timestamp per user with LAG.
  2. flagged – marks a row as the start of a new session when the gap exceeds 30 minutes (or it’s the first row).
  3. sessionized – runs a running total of those flags; each increment creates a new session_id.
  4. The final aggregation groups by the generated session_id to produce session boundaries and metrics.

Why This Works Better Than Loops

Window functions operate on the whole partition in a single pass. The optimizer can push predicates, use indexes on (user_id, event_ts), and avoid materializing intermediate temp tables. In my benchmarks on a 10 million‑row dataset, the CTE version finished in under 2 seconds, while a cursor‑based approach took minutes.

Tip: If your platform lacks INTERVAL arithmetic, replace the gap check with a numeric difference (e.g., EXTRACT(EPOCH FROM (event_ts - prev_ts)) > 1800).

Performance Considerations

  • Ensure a covering index on (user_id, event_ts) so the LAG and ordering are cheap.
  • Keep the CTEs shallow; each adds a planning step. Most planners merge them automatically, but you can hint with /*+ MATERIALIZE */ if needed.
  • For extremely high cardinality, consider partitioning the table by user_id or time to limit the window size.

Common Gotchas

Watch out for time‑zone aware timestamps — LAG respects the stored zone, but the interval comparison must be in the same zone. Also, ROWS UNBOUNDED PRECEDING is the default frame for SUM with an ORDER BY, yet being explicit prevents surprises when you later add a RANGE clause.

Extending the Pattern

The same skeleton solves island problems like “consecutive days of login” or “continuous sensor readings above a threshold”. Swap the gap condition, keep the running total, and you have a reusable template.

Final Thoughts

Gap-and-island analysis used to be a showcase for procedural code. With CTEs and window functions it becomes a declarative one‑liner that the optimizer loves. Next time you see a loop that walks rows to detect breaks, replace it with this pattern — you’ll ship faster and sleep better.