Advanced

Gaps and Islands and Other Classic Patterns

Use window functions, row-number offsets, anti-joins, and date generation to find consecutive runs, missing periods, sessions, and latest records.

Est. 18–24 min Updated Suggest a fix

// Part 01

Turn ordered rows into meaningful runs

Analytical SQL often asks questions that are not simple totals: Which dates form a consecutive streak? Where are the missing sequence numbers? When did a user’s activity session start and end? These are commonly called islands and gaps. An island is a consecutive run; a gap is a missing interval between observed values.

The key steps are: define the entity and ordering, remove duplicate observations at the chosen grain, identify where continuity breaks, then aggregate each run. A good query states its assumptions—for example, whether one activity row per user per day is expected, what counts as a missing day, and how equal timestamps are ordered.

💡 Note
Prerequisites: ROW_NUMBER, LAG, CTEs, date arithmetic, and grouping. Objective: find consecutive date islands, missing dates, and event sessions with explicit boundaries.

// Part 02

Consecutive date islands with the row-number offset

For distinct consecutive dates, subtract the row number from each date. Within a consecutive run, the difference is constant: January 10 minus row 1 and January 11 minus row 2 produce the same key. A break in the dates changes that key, so grouping by it identifies each island. Partition by the entity whose streaks you are finding.

PostgreSQL — group each user’s consecutive active days
WITH active_days AS (
  SELECT DISTINCT customer_id, order_date AS active_date
  FROM orders
  WHERE order_status = 'Delivered'
), numbered AS (
  SELECT customer_id,
         active_date,
         active_date - ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY active_date
         )::integer AS island_key
  FROM active_days
)
SELECT customer_id,
       MIN(active_date) AS streak_start,
       MAX(active_date) AS streak_end,
       COUNT(*) AS consecutive_days
FROM numbered
GROUP BY customer_id, island_key
ORDER BY customer_id, streak_start;

The DISTINCT step is important: multiple delivered orders on the same date should not count as multiple consecutive days. The result’s grain is one row per customer streak. Add HAVING COUNT(*) >= 3 to keep streaks of at least three dates.

🎯 Pro Tip
This date-minus-row-number technique works when a day is the unit of continuity. For consecutive integer IDs, use id - ROW_NUMBER(). For workdays, business calendars, or irregular schedules, define continuity against the appropriate calendar rather than assuming every calendar date is expected.

// Part 03

Find gaps by generating the expected sequence

To find missing dates, first define the expected date range and entity set, then compare that expected grid with observed rows. PostgreSQL’s generate_series can make a date spine. A left anti-join (NOT EXISTS) returns expected dates with no matching activity.

PostgreSQL — dates with no delivered orders
WITH expected_days AS (
  SELECT d::date AS expected_date
  FROM generate_series(
    DATE '2026-01-01', DATE '2026-01-31', INTERVAL '1 day'
  ) AS series(d)
), observed_days AS (
  SELECT DISTINCT order_date
  FROM orders
  WHERE order_status = 'Delivered'
)
SELECT e.expected_date
FROM expected_days AS e
WHERE NOT EXISTS (
  SELECT 1
  FROM observed_days AS o
  WHERE o.order_date = e.expected_date
)
ORDER BY e.expected_date;

For per-store gaps, cross join the date spine with the stores in scope, then compare on both store_id and date. Limit the range before constructing the grid: if there are many entities and years of dates, the expected combinations can become very large. An alternative is to compare each observed date with its successor using LEAD and report only gaps between observations.

PostgreSQL — report gaps between observed dates without a full calendar
WITH observed_days AS (
  SELECT DISTINCT order_date
  FROM orders
  WHERE order_status = 'Delivered'
), neighbors AS (
  SELECT order_date,
         LEAD(order_date) OVER (ORDER BY order_date) AS next_date
  FROM observed_days
)
SELECT order_date + 1 AS gap_start,
       next_date - 1 AS gap_end,
       next_date - order_date - 1 AS missing_days
FROM neighbors
WHERE next_date > order_date + 1
ORDER BY gap_start;

// Part 04

Sessionization: islands separated by a time gap

For clickstreams, an island may be a session rather than a run of dates. Sort each user’s events, use LAG to compare the current timestamp with the previous event, mark the first event or a gap above the chosen inactivity threshold, then cumulatively sum those markers to assign a session number.

PostgreSQL — start a new session after 30 minutes of inactivity
WITH ordered_events AS (
  SELECT user_id, event_id, occurred_at,
         LAG(occurred_at) OVER (
           PARTITION BY user_id
           ORDER BY occurred_at, event_id
         ) AS previous_at
  FROM user_events
), marked AS (
  SELECT *,
         CASE
           WHEN previous_at IS NULL
             OR occurred_at - previous_at > INTERVAL '30 minutes'
           THEN 1 ELSE 0
         END AS starts_session
  FROM ordered_events
), labeled AS (
  SELECT *,
         SUM(starts_session) OVER (
           PARTITION BY user_id
           ORDER BY occurred_at, event_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS session_number
  FROM marked
)
SELECT user_id, session_number,
       MIN(occurred_at) AS session_start,
       MAX(occurred_at) AS session_end,
       COUNT(*) AS event_count
FROM labeled
GROUP BY user_id, session_number
ORDER BY user_id, session_start;

The threshold is a product definition, not a universal SQL constant. Include a stable tie-breaker such as event_id when timestamps match. If events arrive late, a session boundary can change after recalculation; streaming systems may need a lateness policy and a reprocessing window.

// Part 05

Other classic patterns: latest row and deduplication

Many “one row per group” problems are not solved safely with MAX(timestamp) alone: that gives the timestamp but not the other columns from the same row. Rank rows within each business key and retain rank 1, adding a deterministic tie-breaker.

PostgreSQL — keep the latest status event for each order
WITH ranked AS (
  SELECT order_id, status, changed_at, event_id,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY changed_at DESC, event_id DESC
         ) AS rn
  FROM order_status_history
)
SELECT order_id, status, changed_at
FROM ranked
WHERE rn = 1
ORDER BY order_id;

For duplicate cleanup, first identify the true business key and define which row wins. Preview the ranked rows before deleting anything. If a business key is nullable, decide whether NULL values should be treated as equal; SQL’s ordinary equality does not match NULL to NULL, while uniqueness rules also vary by engine and constraint definition.

Preview potential duplicates before deciding what to keep
WITH ranked AS (
  SELECT customer_id, email, created_at, customer_record_id,
         ROW_NUMBER() OVER (
           PARTITION BY lower(email)
           ORDER BY created_at, customer_record_id
         ) AS duplicate_rank
  FROM customer_import
  WHERE email IS NOT NULL
)
SELECT *
FROM ranked
WHERE duplicate_rank > 1
ORDER BY lower(email), duplicate_rank;

// Part 06

Dialect notes and validating assumptions

TaskPostgreSQLOther engines
Date spinegenerate_series(start, stop, interval)SQL Server has date-series options in some versions; MySQL can use a recursive CTE or calendar table; Snowflake commonly uses a generated sequence/date table.
Date minus row numberdate - integer returns a dateUse each engine’s date arithmetic and cast rules; a calendar dimension is often the clearest option.
Session thresholdtimestamp - timestamp compared with INTERVALMySQL uses timestamp/date functions, SQL Server uses DATEDIFF-style logic, and Snowflake has its own date/time functions.
Latest rowROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)The window pattern is broadly supported; explicit deterministic tie-breakers remain essential.

Before choosing a pattern, write down the input grain, the expected output grain, the continuity rule, and the tie policy. Check duplicate rows, NULLs, boundary dates, and an entity with no events. Test on a tiny hand-worked dataset first, then compare counts and boundary cases on production-like data.

⚠️ Important
A gaps-and-islands query is only as correct as its definition of “consecutive.” Calendar days, business days, increasing IDs, and events within a time threshold each need a different continuity rule.

// Part 07

Common mistakes, practice, and next lesson

1. Forgetting to deduplicate to the intended grain

Several orders on one date can inflate a daily streak. Reduce to one row per entity and date before assigning row numbers.

2. Omitting the entity partition

Without partitioning by customer or account, one user’s rows can join another user’s sequence and create false islands.

3. Leaving tied rows unordered

Use a stable unique tie-breaker so row numbering, latest-record selection, and event sessions are reproducible.

4. Building an unbounded expected grid

A cross join of every entity and every date can explode in size. Bound the date range, filter entities, or use neighbor comparisons where appropriate.

Practice tasks:

  1. Find each customer’s consecutive delivered-order dates after deduplicating to one date per customer.
  2. Return all missing dates between two observed order dates. State whether the range endpoints are inclusive.
  3. Assign event session numbers with a configurable inactivity threshold and a stable event tie-breaker.
  4. Return the latest row per order from a status-history table, including ties on timestamp.
  5. For each query, describe one edge case involving NULL, duplicates, or an entity with no observations.

🎯 Key Takeaways

  • ✓Islands are consecutive runs; gaps are missing intervals relative to an explicit expected sequence.
  • ✓The row-number offset groups consecutive dates or integer values into stable runs.
  • ✓Deduplicate to the intended grain before applying consecutive-day logic.
  • ✓LAG plus a boundary flag and cumulative SUM is a general sessionization pattern.
  • ✓ROW_NUMBER with deterministic ordering selects a complete latest row, not just its maximum timestamp.
  • ✓Date generation and arithmetic are dialect-specific; test edge cases and bound expected grids.

Next

Continue into query-plan reading and performance tuning so you can evaluate how these analytical queries run on larger datasets.

Lesson 62 → EXPLAIN & EXPLAIN ANALYZE

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...