🏝️Gaps and islands

Streaks vs missing rungs

Islands are contiguous runs, uptime stretches, consecutive experiment days. Gaps are missing values in a sequence (invoice holes). Both lean on ordering, then a derived group key from window row numbers; half-open time ranges keep off-by-one bugs manageable.

SQL

Islands of consecutive days with activity

WITH flagged AS (
  SELECT day, device_id,
         day - (ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY day))::int AS grp
  FROM (
    SELECT DISTINCT device_id, activity_date::date AS day
    FROM device_events
  ) d
)
SELECT device_id, MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS days
FROM flagged
GROUP BY device_id, grp;

✓Illustrative result set

device_id	start_day	end_day	days
D-1	2026-04-01	2026-04-03	3
D-1	2026-04-07	2026-04-08	2

SQL

Gap detection (missing integers)

WITH seq AS (
  SELECT generate_series(1,7) AS id
), holes AS (
  SELECT id FROM seq
  EXCEPT
  SELECT invoice_id FROM invoices WHERE invoice_id BETWEEN 1 AND 7
)
SELECT id AS missing_invoice_id FROM holes ORDER BY id;

✓Illustrative result set

missing_invoice_id
3
6

Messy time boundaries

Duplicate timestamps breaking streak boundaries.
Inclusive/exclusive range mistakes across time zones.
Huge partitions without indexes aligned to ORDER BY keys, window sorts spill.

Normalize, then subtract

Prefer half-open intervals; dedupe events before streak logic when needed.
Test ties and missing rows explicitly; document sampling cadence for sensors.
Materialize streak ids in batch if interactive latency suffers.

🧩The Core Trick: Row Number - Value

Creating a constant ID where none exists.

The Difference Constant

How do you group "Consecutive Days"? If you subtract ROW_NUMBER() from the Date, a continuous run will produce the same constant value. This value doesn't mean anything logically, but it acts as a perfect GROUP BY key for your island.

Day 1, 2, 3 → Row 1, 2, 3 → (1-1), (2-2), (3-3) = 0, 0, 0
Day 7, 8 → Row 4, 5 → (7-4), (8-5) = 3, 3

Algorithm	Complexity	Best For
Row Number Difference	O(N log N) - Sorting cost.	Traditional Gaps & Islands on integers or dates.
Lag/Lead Comparison	O(N log N)	Identifying where a streak breaks (e.g., status changes).

🏹Alternative: Lag/Lead Comparison

Finding the edges of the island.

Instead of creating a group key, you can use LAG() to see if the previous row has a different value than the current one. This is ideal for State Tracking (e.g., "How long was the machine in 'ERROR' state?").

Performance: Sorting

Gaps & Islands logic is heavily dependent on ORDER BY. If you are running this on a billion rows without an index that covers both the PARTITION and ORDER keys, your query will time out during the sort phase.

Alternatives

For dense integer gaps at scale, anti-join patterns or bitmaps can beat generate_series, pick the tool after you know cardinality and range width.

TL;DR

Gaps and islands are classic window-function problems on ordered data: gaps find missing values in a sequence (IDs, dates); islands find contiguous runs (sessions, uptime streaks). Use ROW_NUMBER() / DENSE_RANK() tricks or difference-from-partition identifiers to label groups, then aggregate. Correctness depends on defining order keys and whether your series is dense or sparse.