Gaps and islands at the correct calendar grain

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Gaps and islands at the correct calendar grain

Concept and contract

Mark sequence breaks only after deduplicating observations to the entity-day grain.

A gaps-and-islands task groups consecutive observed periods into runs. This fixture supplies a small validated calendar with ordinals one through four. Company A has role publications on days one and four, so those dates belong to two separate islands under a consecutive-calendar-day contract.

The ordinal comes from an explicit calendar dimension, not from subtracting integers that happen to look like YYYYMMDD. That shortcut fails at month boundaries. A real calendar dimension or engine-specific typed date arithmetic must preserve actual chronological adjacency.

Mechanism and worked example

The present stage selects distinct publication days and their calendar ordinals.

The present stage selects distinct publication days and their calendar ordinals. LAG finds the preceding observed ordinal. A gap larger than one starts a new island; the first row also starts one. A cumulative SUM of those start markers assigns island identifiers.

Each stage has a declared grain: one entity-day, then one boundary marker per day, then one island annotation per day. The output keeps dates rather than collapsing islands into ranges. Grouping to start and end dates is an optional extension after the core sequence is correct.

Complete worked example

sql

WITH calendar(day,n) AS (VALUES ('2026-10-01',1),('2026-10-02',2),('2026-10-03',3),('2026-10-04',4)),
 present AS (SELECT DISTINCT r.published AS day,c.n FROM roles r JOIN calendar c ON c.day=r.published WHERE r.company_id='A'),
 marked AS (SELECT day,n,CASE WHEN n-LAG(n) OVER (ORDER BY n)=1 THEN 0 ELSE 1 END AS start FROM present)
SELECT day,SUM(start) OVER (ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
FROM marked ORDER BY day;

Reasoning: A has two distinct publication days separated by a calendar gap.

Counterexamples and common mistakes

The counterexample omits DISTINCT before the windows.

The counterexample omits DISTINCT before the windows. Company A has two role records on the first day, so raw role grain creates repeated calendar positions and incorrect boundary markers. A clever window formula cannot repair an input grain that does not match the sequence question.

Use test data with repeated observations on one day and a real missing day. A clean consecutive one-row-per-day sample would hide both mistakes. The stable calendar ordinal order becomes unique after deduplication, so the running marker sum has a deterministic sequence.

Counterexample: a plausible but wrong query

sql

WITH calendar(day,n) AS (VALUES ('2026-10-01',1),('2026-10-02',2),('2026-10-03',3),('2026-10-04',4)),
 present AS (SELECT r.published AS day,c.n FROM roles r JOIN calendar c ON c.day=r.published WHERE r.company_id='A'),
 marked AS (SELECT day,n,CASE WHEN n-LAG(n) OVER (ORDER BY n)=1 THEN 0 ELSE 1 END AS start FROM present)
SELECT day,SUM(start) OVER (ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
FROM marked ORDER BY day,island;

Reasoning: Raw role duplicates create extra sequence positions and false island starts.

Independent practice: small query

Adapt the complete pipeline to company B.

Adapt the complete pipeline to company B. Its publication days are the second and fourth, with multiple roles on the second. Predict the second day in island one and the fourth day in island two. Keep day deduplication and the same calendar adjacency policy.

This task changes only the company predicate. Work out the two observed days by hand before running the query. Do not treat the two role records on the second as two consecutive calendar positions or change the gap rule to match record counts.

Step 1: List B's distinct publication days. Step 2: Keep the existing calendar adjacency and running boundary sum.

Practice starter: solve before reading the solution

sql

-- Reuse the deduplicated day pipeline for company B.
-- Keep the same calendar and consecutive-day boundary rule.

Reasoning: Change the company while preserving calendar and day grain.

Hint 1: Two B roles on the second must become one sequence position.
Hint 2: The missing third day separates the two observed days into islands.

Solution, tests and trade-offs

The solution returns two B dates with island identifiers one and two.

The solution returns two B dates with island identifiers one and two. The labels are local sequence identifiers, not durable business keys. Inserting an earlier observed period can renumber later islands, so do not persist those numbers as an immutable external identity without another contract.

Acceptance compares dates and island assignments, including the gap. An optional summary can group those annotations into date ranges, but preserve the underlying day membership so a range does not conceal a duplicated or omitted date.

Practice solution and acceptance checks

sql

WITH calendar(day,n) AS (VALUES ('2026-10-01',1),('2026-10-02',2),('2026-10-03',3),('2026-10-04',4)),
 present AS (SELECT DISTINCT r.published AS day,c.n FROM roles r JOIN calendar c ON c.day=r.published WHERE r.company_id='B'),
 marked AS (SELECT day,n,CASE WHEN n-LAG(n) OVER (ORDER BY n)=1 THEN 0 ELSE 1 END AS start FROM present)
SELECT day,SUM(start) OVER (ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
FROM marked ORDER BY day;

Reasoning: B's second and fourth days form two separate islands.

Interview reasoning and next step

In an interview, derive the boundary condition from the business meaning of consecutive.

In an interview, derive the boundary condition from the business meaning of consecutive. It might mean calendar days, working days or ordered observations; each requires a different adjacency relation. State the calendar and deduplication grain before presenting the cumulative-sum pattern.

The next lessons connect correct query results to safe bound values, execution plans and atomic changes. These operational topics still depend on the same discipline: explicit contracts, small disposable fixtures and evidence for what was actually tested.

Optional stretch — after the core task works: Summarize each island to a start and end date while preserving a test of its day membership.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Mark sequence breaks only after deduplicating observations to the entity-day grain.