What Counts as a Gap vs an Island
An island is a maximal stretch of rows with no break between any 2 neighbours. A gap is the empty space between 2 islands: the values that would have continued the sequence and are missing. In the sequence 1, 2, 3, 7, 8, 12 there are 3 islands and 2 gaps, and the gaps are made of values that appear in no row at all, which is the part that trips people up. You cannot SELECT a gap, because the gap has no rows; the only thing the query can do is find where each island stops.
| n | next value present | what sits between | verdict |
|---|---|---|---|
| 1 | 2 | nothing | island continues |
| 2 | 3 | nothing | island continues |
| 3 | 7 | 4, 5, 6 missing | island ends, gap of 3 |
| 7 | 8 | nothing | island continues |
| 8 | 12 | 9, 10, 11 missing | island ends, gap of 3 |
| 12 | none | end of data | island of 1 row |
The question defines the break
What counts as a break is set by the question, and the first job is to pull that definition out of the wording. "Consecutive days" means the expected step is 1 day and anything larger is a break. "Contiguous invoice numbers" means the step is 1. "Consecutive hours of readings" means 1 hour. The difference trick in section 3 works for any sequence that is supposed to step by a fixed amount, because it subtracts the position from the value, and a fixed step is what makes that subtraction land on the same number row after row.
| question wording | sequence column | expected step | a break is |
|---|---|---|---|
| longest login streak | login_date | 1 day | a calendar day with no login |
| contiguous invoice ranges | invoice_no | 1 | a missing number |
| unbroken hourly readings | reading_hour | 1 hour | an hour with no reading |
| consecutive booked nights | night | 1 day | an unbooked night |
Some questions define a break by something other than a missing value: a status that changes from active to cancelled, or 2 events more than 30 minutes apart counting as separate sessions. Those are the same family and need a different way of building the island key, which later lessons in this track cover. Here, say the step back to the interviewer as a question: "so 2 logins on the same day collapse to 1 day, and a day with no login breaks the streak?" That question costs 5 seconds and prevents the most common wrong answer, which is a correct query built on a definition the interviewer never gave.
Counting what is missing
Count what the question asks for. An island's length is the number of rows in it, so the island from 7 to 8 has length 2. A gap's size is the number of values skipped, which is the next value minus the current value minus 1, so the gap between 3 and 7 is 3 missing values and not 4. Candidates who report the gap as 4 have counted the fence posts instead of the fence, and an interviewer who put a gap of 3 in the sample is checking for exactly that.
Islands of 1 and the edges of the data
A row with no neighbour on either side is an island of its own, with length 1. The value 12 in the sequence above is one, and a user who logged in once has a streak of 1. Whether the question wants those rows depends on who is asking: a leaderboard of streaks includes them, a report of "streaks worth celebrating" may set a minimum of 2 or 3. The edges of the data are not gaps. Nothing is missing before the first row or after the last one, because there is no island on the far side for a gap to sit between, so a sequence that starts at 7 has no gap from 1 to 6 and a user whose last login was in March has no gap running up to today.
Saying the definition out loud
A strong answer states the 3 facts the definition pins down before any SQL: what the sequence column is, what the expected step is, and whether islands of 1 belong in the output. "The sequence is login_date, the step is 1 day, and a single login is a streak of 1 unless you tell me otherwise." Every later choice, including the exact arithmetic of the group key and the aggregates at the end, follows from those 3 facts, so stating them first lets the interviewer check your plan before you commit to it.
About this lesson
This section is part of the Gaps and Islands: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.