All lessons

Module 8: Patterns analysts use every week · Lesson 8.4 · about 30 min

Gaps and islands: streaks and missing days

Find runs of consecutive days (islands) and the holes between them (gaps) with one neat trick: date minus row number. Practise on Northbay's online shop, store by store.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 8 · about 30 minutes · read first: 6.2, 6.4, 5.5. You’ll be able to find streaks of consecutive days, the gaps between them, and days with no data at all.

Inside a streak, the date and the row number both go up by one, so date minus row number stays the same and becomes a label for the streak.

In everyday terms: a paper chain. Each day with sales is a link; where a day is missing, the chain breaks. The trick gives every link in one piece of chain the same tag, so you can count the pieces and measure each one.

1. Why this matters

“What is our longest run of days with online sales?”, “When was this sensor offline?”, “How many days in a row has this user logged in?” These are gaps-and-islands problems. They look hard because SQL has no “streak” function, but one short trick solves nearly all of them. It is a classic medium-to-hard interview question, and once you know it you will spot it everywhere.

2. Islands and gaps

Picture the days a store had sales as dots on a calendar. An island is a run of consecutive days with sales. A gap is the run of empty days between two islands. In early March 2024, Northbay’s online shop had sales on these days:

-- try
SELECT DISTINCT order_date
FROM orders
WHERE store_id = 12 AND status = 'completed'
  AND order_date BETWEEN '2024-03-01' AND '2024-03-15'
ORDER BY order_date

Eight days. By eye, the islands are 1–2 March, 5 March, 9–11 March and 13–14 March. The query below finds them without looking.

3. The trick: date minus row number

Number the days in order with ROW_NUMBER(). Inside an island, the date goes up by one day per row, and so does the row number. So date minus row number stays the same for the whole island, and jumps when a gap starts. That difference becomes a group label.

Press Run, then compare rn and grp row by row:

-- try
WITH days AS (
  SELECT DISTINCT order_date
  FROM orders
  WHERE store_id = 12 AND status = 'completed'
    AND order_date BETWEEN '2024-03-01' AND '2024-03-15'
)
SELECT order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS rn,
       date(order_date, '-' || ROW_NUMBER() OVER (ORDER BY order_date) || ' days') AS grp
FROM days
ORDER BY order_date

1 and 2 March both give grp = 29 February. 5 March gives 2 March. 9, 10 and 11 March all give 5 March. The value of grp means nothing on its own; it only has to be equal within an island and different between islands. (SQL Server: DATEADD(day, -ROW_NUMBER() OVER (ORDER BY order_date), order_date); PostgreSQL: order_date - ROW_NUMBER() OVER (ORDER BY order_date)::int on a DATE column.)

Now group by the label: each group is one island.

-- try
WITH days AS (
  SELECT DISTINCT order_date
  FROM orders
  WHERE store_id = 12 AND status = 'completed'
    AND order_date BETWEEN '2024-03-01' AND '2024-03-15'
),
labelled AS (
  SELECT order_date,
         date(order_date, '-' || ROW_NUMBER() OVER (ORDER BY order_date) || ' days') AS grp
  FROM days
)
SELECT MIN(order_date) AS starts, MAX(order_date) AS ends, COUNT(*) AS days
FROM labelled
GROUP BY grp
ORDER BY starts

Four islands, exactly as we saw by eye. The DISTINCT in the first CTE matters: a day with three orders must count once, or the row numbers run ahead of the dates and the trick breaks.

Maths and principle

Principle: inside an island, date and row number are both arithmetic sequences with step 1, so their difference is constant. Between islands, the date jumps by more than 1 while the row number still rises by 1, so the difference changes.

Equation: for the i-th day dᵢ in sorted order, the label is gᵢ = dᵢ − i. If dᵢ₊₁ = dᵢ + 1 then gᵢ₊₁ = gᵢ (same island). If dᵢ₊₁ = dᵢ + k with k > 1, then gᵢ₊₁ = gᵢ + (k − 1), and k − 1 is exactly the length of the gap. Grouping is then γg; MIN(d), MAX(d), COUNT(*).

Analogy: two runners on a track at the same speed keep the same distance between them. If one stops for a few seconds (a gap), the distance changes and stays changed.

Try it: dates 10, 11, 12, 15, 16 with row numbers 1–5. What are the labels? (9, 9, 9, 11, 11: two islands, and the gap is 11 − 9 = 2 days long.)

Island
A run of consecutive days (or numbers) that all have data.
Gap
The run of missing days between two islands.
Group label (grp)
date − ROW_NUMBER(). Equal inside an island, different between islands. Its value means nothing else.
DISTINCT days
Each day must appear once, or the row numbers run ahead of the dates.
LEAD(day)
The next day with data, put beside each day, so the gap is next − day − 1.
Physical meaning
ROW_NUMBER gives each sales day a position. Subtracting it from the date shifts every day in a streak back to the same date, so GROUP BY that shifted date collects each streak into one row with its start, end and length.

“Just use LAG and check if yesterday had sales.” That tells you where a streak starts, but not which streak each day belongs to, so you can’t measure lengths without more steps. Date minus row number labels every day in one pass. And always start from DISTINCT days: two orders on one day break the trick.

Watch it happen, one step at a time:

Distinct sales days.
day
1
2
5
9
10
11
Number them.
dayrn
11
22
53
94
105
116
Label = day − rn. Same label inside a streak.
dayrngrp
110
220
532
945
1055
1165
GROUP BY grp. Each group is one island.
startsendsdays
122
551
9113
The gaps. The label jumps by the gap length: 0 → 2 means days 3–4 are missing (2 days); 2 → 5 means days 6–8 (3 days).

Any live box with a SELECT has a Show how it runs button that animates the real query on Northbay, row by row.

4. Gaps with LEAD

When you want the gaps themselves, LEAD is simpler: put the next sales day beside each sales day, and keep the rows where the next one is more than a day away (Lesson 6.4).

-- try
WITH days AS (
  SELECT DISTINCT order_date
  FROM orders
  WHERE store_id = 12 AND status = 'completed'
),
nexts AS (
  SELECT order_date, LEAD(order_date) OVER (ORDER BY order_date) AS next_day
  FROM days
)
SELECT order_date AS last_sale, next_day AS next_sale,
       julianday(next_day) - julianday(order_date) - 1 AS empty_days
FROM nexts
WHERE julianday(next_day) - julianday(order_date) > 1
ORDER BY empty_days DESC, last_sale
LIMIT 5

The longest quiet spell online was 6 days, between 15 and 22 August 2024.

5. Worked example

Question: what was the online shop’s longest streak of days with sales?

  1. Take the 554 distinct days on which store 12 had a completed order (out of 731 days in 2024–2025).
  2. Label each day with date minus row number and group by the label: that gives 127 islands.
  3. Sort the islands by length. The longest are 19 days, twice: 9–27 January 2025 and 13–31 December 2025.
-- try
WITH days AS (
  SELECT DISTINCT order_date
  FROM orders
  WHERE store_id = 12 AND status = 'completed'
),
labelled AS (
  SELECT order_date,
         date(order_date, '-' || ROW_NUMBER() OVER (ORDER BY order_date) || ' days') AS grp
  FROM days
)
SELECT MIN(order_date) AS starts, MAX(order_date) AS ends, COUNT(*) AS days
FROM labelled
GROUP BY grp
ORDER BY days DESC, starts
LIMIT 5

Insight: “Online sales have become nearly daily in busy months; both 19-day runs are in 2025, and one is the holiday season.” For a physical store the streaks are much shorter, because Northbay’s sample data spreads a few orders a day across eleven stores.

6. Practice

-- exercise: Find each store's longest streak of consecutive days with completed orders. Show store_id and the longest streak in days, longest first (ties by store_id).
-- hint: Add PARTITION BY store_id to the ROW_NUMBER, group islands by store_id and grp, then take MAX of the island lengths per store.
SELECT store_id, COUNT(DISTINCT order_date) AS sales_days
FROM orders
WHERE status = 'completed'
GROUP BY store_id
-- answer:
WITH days AS (
  SELECT DISTINCT store_id, order_date
  FROM orders
  WHERE status = 'completed'
),
labelled AS (
  SELECT store_id, order_date,
         date(order_date, '-' || ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY order_date) || ' days') AS grp
  FROM days
),
islands AS (
  SELECT store_id, grp, COUNT(*) AS len
  FROM labelled
  GROUP BY store_id, grp
)
SELECT store_id, MAX(len) AS longest_streak
FROM islands
GROUP BY store_id
ORDER BY longest_streak DESC, store_id
-- exercise: How many days in 2024 had no completed online order (store 12)? Return one number.
-- hint: Build a calendar of every day in 2024 with WITH RECURSIVE, LEFT JOIN the online sales days, and count the rows where the join found nothing.
SELECT COUNT(DISTINCT order_date) AS days_with_sales
FROM orders
WHERE store_id = 12 AND status = 'completed' AND order_date < '2025-01-01'
-- answer:
WITH RECURSIVE cal(d) AS (
  SELECT '2024-01-01'
  UNION ALL
  SELECT date(d, '+1 day') FROM cal WHERE d < '2024-12-31'
),
sales AS (
  SELECT DISTINCT order_date
  FROM orders
  WHERE store_id = 12 AND status = 'completed'
)
SELECT COUNT(*) AS days_without_sales
FROM cal
LEFT JOIN sales s ON s.order_date = cal.d
WHERE s.order_date IS NULL

Try it on your own data: check a log of dates for missing days with [[date-calculator]], or run these queries on your CSV in [[sql-playground]].

  • An island is a run of consecutive days with data; a gap is the empty run between two islands.
  • Label each day with date − ROW_NUMBER(); group by the label to get one row per island.
  • Start from DISTINCT days so each day is numbered once.
  • For the gaps themselves, LEAD(day) beside each day and keep rows where the next day is more than 1 away.
  • The jump in the label between two islands equals the length of the gap.

New words: island, gap, streak, group label.

Check yourself

1In the gaps-and-islands trick, why is date minus ROW_NUMBER() the same for every day in a streak?
2Which window function is the simplest way to find gaps between days with sales?
3You want the days a store had no sales at all. What do you need?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.