Skip to content
Tutorials Center
My learning

Topics

Lesson 55 of 125 ยท Window Functions

Gaps and Islands

Find consecutive runs and missing ranges in SQL with window functions. Learn the ROW_NUMBER difference trick, login streaks, gap detection and status changes.

Some of the most useful questions in analytics are about runs. How many days in a row did a user log in? Which invoice numbers are missing from the sequence? For how long was a server in the "down" state before it recovered? The data points that belong together form an island; the holes between islands are gaps.

Plain GROUP BY can't answer these questions, because what groups rows together is not a column value but their position in a sequence. The classic solution uses window functions: number the rows, compare that number with the data, and let a constant difference reveal which rows belong to the same run. Once you've seen the trick, you'll recognize it in all kinds of problems.

flowchart LR
  A["Sorted values<br/>1 2 3 7 8 12"] --> B["ROW_NUMBER<br/>1 2 3 4 5 6"]
  B --> C["value - row number<br/>0 0 0 3 3 6"]
  C --> D["GROUP BY the difference<br/>islands: 1-3, 7-8, 12-12"]

Inside one island, value and row number grow together, so their difference stays constant. A jump in the data changes it.

Islands in a sequence of numbers

Imagine order numbers that should be consecutive but have holes. Sort the ids, give each row a row number, and subtract. Within a run of consecutive ids, both the id and the row number go up by one at every step, so the difference never changes. When an id is skipped, the id jumps by more than one while the row number grows by one, and the difference goes up. Grouping by the difference gives you one group per island:

SQL Islands of consecutive numbers

Press Run (or Ctrl + Enter) to see the result here.

You get four islands: 1-3, 7-8, 12-15 and 20-20. The grp value itself is meaningless (it is just a label); what matters is that rows in the same island share it. The MIN and MAX give each island's boundaries.

This only works if the values are unique. With duplicates, ROW_NUMBER would count a repeated value as a step forward while the value stays put, which breaks the constant difference. When duplicates are possible, remove them first with SELECT DISTINCT or use DENSE_RANK() instead of ROW_NUMBER().

Islands of dates: login streaks

Dates are not integers, but you can still subtract a number of days from a date. For each user, number their distinct login days and subtract that number of days from the date. Consecutive days produce the same result, which is the anchor date of the streak:

SQL Consecutive login days per user

Press Run (or Ctrl + Enter) to see the result here.

Notice the PARTITION BY user_name: every user gets their own numbering, so each user's streaks are found independently. Alice has three islands (3 days, 2 days, 1 day) and Bob has two (1 day and 5 days). Date arithmetic differs between systems: in PostgreSQL you'd write day - ROW_NUMBER() OVER (...) * INTERVAL '1 day', in MySQL DATE_SUB(day, INTERVAL n DAY); the idea is identical. See date functions.

Once you have the islands as a CTE, questions become one-liners. The longest streak per user is a second aggregation over the first, or a top-N per group with ROW_NUMBER() ordered by length. A user is currently on a streak if the latest island ends today.

Finding the gaps

Sometimes you want the holes, not the runs: which ticket numbers are missing, which days have no data? There are two approaches. The first compares each row with the next using LEAD. If the next value is more than one step away, everything in between is a gap:

SQL Missing ranges with LEAD

Press Run (or Ctrl + Enter) to see the result here.

The gaps are 4-6, 9-11 and 16-19. The last row has no next value (LEAD returns NULL), and a comparison with NULL is never true, so the final id is correctly skipped. Gaps before the first or after the last value aren't visible this way, because there is nothing to compare against; if you need them, decide the expected range up front (say 1 to 25) and add those boundaries explicitly.

The second approach generates the full list of expected values with a recursive CTE and uses an anti-join to list what's missing. That one is better when you want every missing value individually; see Time Series and Calendar Tables.

Islands defined by a change of state

Not every island is a run of consecutive numbers. Often rows are in time order and an island is "the stretch where the status stayed the same". Think of a monitoring log that records only when something is checked: the status was up, up, down, down, down, up. The islands are the unbroken stretches of the same status.

The trick here is different: flag every row whose status differs from the previous row's, then take a running total of the flags. The running total goes up by one at every change, so it is a label for each island.

flowchart LR
  A["LAG(status)<br/>previous row's status"] --> B["flag = 1 when status<br/>differs from previous"]
  B --> C["SUM(flag) OVER (ORDER BY time)<br/>running total"]
  C --> D["GROUP BY the running total<br/>one group per island"]

Change detection plus a running total turns state changes into island labels.

SQL How long was each status in effect?

Press Run (or Ctrl + Enter) to see the result here.

There are four islands: up (2 checks), down (3), up (2) and down (1). Because the first row has no previous row, LAG returns NULL, the = comparison is not true, and the CASE falls into ELSE 1, which correctly starts island 1. This pattern also solves "group consecutive events by user", "stretches of the same price" and "runs of heads in a coin-flip log".

Which technique when?

Situation Technique
Consecutive integers or dates, one row per value Value minus ROW_NUMBER(), group by the difference
Consecutive dates with duplicates DISTINCT first, or DENSE_RANK()
Runs of the same state or category LAG to flag changes, then a running SUM
Missing values in a numeric or date sequence LEAD and compare, or generate the sequence and anti-join

Whatever the technique, watch three details. Define exactly what "consecutive" means (calendar days? business days? within 30 minutes?). Decide how ties and duplicates behave before you write the query. And remember that the whole thing relies on a deterministic order: add a tiebreaker column to the ORDER BY when the sort key can repeat. For islands based on time gaps instead of exact steps, such as web sessions, see Sessionization, Funnels and Retention.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In the ROW_NUMBER difference trick, why do rows of one island share the same value of (id - row_number)?

    Inside a run of consecutive values, id and row number both grow by one per row, so the difference is constant. A skipped value makes id jump while the row number does not, which changes the difference.
  2. 2. Which window function finds the next value so you can detect a missing range?

    LEAD returns the value from the following row. If next_id - id is greater than 1, the numbers between them are missing.
  3. 3. A log lists status values in time order. How do you label each stretch of the same status?

    GROUP BY status would merge separate stretches of the same status. A running sum of change flags increases at every switch, giving each stretch its own label.
  4. 4. Why should duplicates be removed before applying the ROW_NUMBER difference trick to dates?

    With duplicates the row number moves forward while the date stays the same, so rows of the same streak no longer share one anchor value. Use DISTINCT or DENSE_RANK() first.

Summary

  • An island is a run of rows that belong together by position; a gap is the hole between two islands.
  • For consecutive numbers or dates, subtract ROW_NUMBER() from the value (or subtract that many days from the date) and group by the result.
  • Add PARTITION BY to find islands separately for each user, device or customer.
  • LEAD (or LAG) finds gaps by comparing each row with its neighbor.
  • For runs of an unchanged state, flag changes with LAG and take a running SUM to number the islands.
  • Remove duplicates first and always use a deterministic ORDER BY.

Next: INSERT, where we leave querying behind and start adding data to tables.