In SQL, what is the gaps-and-islands problem, and what counts as an island?
answer
- runs of consecutive values in ordered data
- the holes between those runs
- GROUP BY needs a key nothing supplies
- you synthesize a run id per island
basics
~20 sGaps and islands names a family of SQL problems over an ordered column: islands are maximal runs of consecutive values, gaps are the stretches missing between them. The work is inventing a group key per run.
solid answer
~50 sGiven rows ordered by some column — dates, ids, or a repeating status — an **island** is a maximal run of consecutive rows, and a **gap** is the missing stretch between two islands. Typical asks: the longest consecutive-login streak, the contiguous date ranges a subscription was active, which invoice numbers are missing. The difficulty is that `GROUP BY` needs a column saying which run each row belongs to, and no such column exists in the data — every solution synthesizes one. Two standard constructions do it: subtract `ROW_NUMBER()` from the value so each run shares a constant difference, or use `LAG()` to flag where a run breaks and take a running `SUM()` of that flag as an island id. You must also state what *consecutive* means: step of exactly one, next calendar day, or within some tolerance.
go deeper
Be able to recognise the shape: rows ordered by date or id, and the ask is about runs or about what is missing. Know the words island and gap and be able to point at both in a short list of values.
Explain why GROUP BY alone cannot express it, and name the two standard run-id constructions: value minus ROW_NUMBER(), and a LAG-based break flag with a running SUM.
Show that you pin the definition down before writing SQL — the step size, whether duplicates collapse, whether single rows count as islands, and how the edges of the data are treated. Interviewers plant one of these ambiguities on purpose.
Weigh whether run boundaries belong in ad-hoc queries at all. Frequently used contiguous ranges or sessions are worth materialising once, in a table or a scheduled job, so every downstream report reads the same agreed definition of a run.
## The shape of the problem You have a table whose rows sit in a natural order — a timestamp, a date, an auto-assigned id, a meter reading number. Some values are present and some are not. An **island** is a maximal run of rows whose ordered values follow one another without interruption; a **gap** is the stretch of missing values between two islands. "Maximal" matters: `1, 2, 3` is one island, not three overlapping ones, because the run is extended as far as it can go in both directions. With values `1, 2, 3, 7, 8, 11` there are three islands (`1–3`, `7–8`, `11`) and two gaps (`4–6`, `9–10`). A single isolated value is still an island of length one, and the empty stretches before the smallest value or after the largest are usually *not* counted as gaps unless the question supplies explicit bounds. ## Where it shows up - Longest streak of consecutive days a user logged in. - Contiguous date ranges over which a subscription, price, or employee assignment stayed in one state — the row-per-day data collapsed into `from`/`to` ranges. - Which invoice or ticket numbers were never used. - Splitting a click stream into sessions, where a new session begins after a period of inactivity. - Collapsing consecutive rows carrying the same status into one run ("the machine was DOWN from 09:14 to 09:52"). The last two are gaps-and-islands even though nothing numeric is missing: the *break condition* is a time tolerance or a value change rather than a skipped integer. ## Why plain grouping does not work `GROUP BY` collapses rows that share a value in some column. To collapse an island you would need a column whose value is the same for every row of a run and different across runs — a run id. That column does not exist in the source data, and it cannot be derived from any single row in isolation: whether `2026-03-05` starts a new run depends entirely on whether `2026-03-04` is also present. That is why the pattern lives with window functions: a window function is the tool that lets a row see its neighbours in a defined order. Sorting alone does not help either. `ORDER BY login_date` puts the rows next to each other on screen, but SQL result rows have no notion of "the row above me"; only a window function gives you that. ## The two standard constructions **Value minus row number.** Number the rows in order with `ROW_NUMBER()` and subtract the row number from the value. Inside a run both increase by one per row, so the difference stays constant; when the sequence jumps, the difference jumps too. `GROUP BY` that difference and each group is exactly one island. For dates you subtract *n days* rather than *n*. The difference itself is a meaningless number — only its constancy is used. **Break flag plus running sum.** Use `LAG()` to compare each row with its predecessor, emit `1` where the run breaks and `0` otherwise, then take a running `SUM()` of that flag in the same order. The running sum only increments at breaks, so it acts as a run id that starts at 1 and counts upward. This form is more general because the break condition can be anything you can write in SQL — a tolerance ("more than 30 minutes since the previous event"), a value change, a business rule. **Finding the gaps** is the mirror image and is usually easier: `LEAD()` gives each row the next existing value, and any row where the next value is more than one step ahead marks a gap whose bounds are `current + 1` and `next − 1`. ## Define "consecutive" before you write SQL Most wrong answers come from an undefined problem, not bad syntax. Pin down: what is the step (one integer? one calendar day? one business day?); do duplicate values collapse or break a run; should a run of length one count; are weekends and holidays holes or not; are gaps before the first row and after the last row in scope. Each of those changes the query, and interviewers frequently plant one of them deliberately — multiple logins on the same day is the classic trap, because it silently breaks the row-number arithmetic unless you deduplicate first.
- Why can a query not decide from a single row whether that row begins a new island?Because the answer depends on the neighbouring value: `2026-03-05` starts a run only if `2026-03-04` is absent. Nothing stored in the row itself carries that information, so you need a window function that can see the previous or next row in a defined order — or an arithmetic trick over the row's position within the ordered set.
- Is 'consecutive rows with the same status' really the same problem as 'consecutive dates'?Yes — only the break condition differs. In both cases you want maximal runs and need a run id to group by. For dates the break is a skipped step; for statuses the break is a change in value; for sessions the break is a time gap over a threshold. The `LAG()` flag plus running `SUM()` construction handles all three unchanged.
- Do gaps before the first value or after the last value count?Not by default. Techniques based on comparing existing rows can only see stretches *between* rows that exist. If the question means 'which invoice numbers from 1 to 1000 are unused', you must supply those bounds explicitly, typically by generating the full range and anti-joining, rather than by reading gaps off the stored rows.
saying these in an interview costs you the question
- Thinks DISTINCT or a plain GROUP BY groups consecutive values
- Confuses islands with duplicate rows in the column
- Assumes ORDER BY alone puts rows into runs
- Says the difficulty is sorting rather than deriving a run id
- Never asks what 'consecutive' means for this data