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.
// 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.
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.
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.
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.
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.
// 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.
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.
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.
// Part 06
Dialect notes and validating assumptions
| Task | PostgreSQL | Other engines |
|---|---|---|
| Date spine | generate_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 number | date - integer returns a date | Use each engine’s date arithmetic and cast rules; a calendar dimension is often the clearest option. |
| Session threshold | timestamp - timestamp compared with INTERVAL | MySQL uses timestamp/date functions, SQL Server uses DATEDIFF-style logic, and Snowflake has its own date/time functions. |
| Latest row | ROW_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.
// 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:
- Find each customer’s consecutive delivered-order dates after deduplicating to one date per customer.
- Return all missing dates between two observed order dates. State whether the range endpoints are inclusive.
- Assign event session numbers with a configurable inactivity threshold and a stable event tie-breaker.
- Return the latest row per order from a status-history table, including ties on timestamp.
- 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 ANALYZEHELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
