Streaks: gaps and islands
Gaps and islands problems ask about runs: the longest streak of months a customer ordered in, or the days a website was down. The trick is to give each row a number that stays the same along a run.
Number each month (2026 × 12 + 10 for October 2026, and so on), then subtract a ROW_NUMBER.
Along an unbroken run, both go up by one each step, so the difference stays the same:
| month number | ROW_NUMBER | difference |
|---|---|---|
| 24301 | 1 | 24300 |
| 24302 | 2 | 24300 |
| 24303 | 3 | 24300 |
| 24306 | 4 | 24302 |
The first three months are one run (an island); the gap before 24306 starts a new one. Group by the difference and count:
WITH months AS (
SELECT DISTINCT customer_id, year(order_date) * 12 + month(order_date) AS m
FROM orders
),
runs AS (
SELECT customer_id,
m - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY m) AS run_id
FROM months
)
SELECT customer_id, run_id, COUNT(*) AS months_in_a_row
FROM runs
GROUP BY customer_id, run_idDISTINCT matters: two orders in the same month would otherwise break the numbering.