Spotting a Question About Runs

Concepts covered: Row Numbering, Grouping

The question rarely contains the word "island". It arrives as "find the longest streak of login days per user", or "show me each stretch of nights the room was booked", or "which invoice numbers are present in contiguous ranges". The data is 1 row per event or per value, and the answer is 1 row per unbroken stretch, which means the query has to collapse many rows into 1 without any column that says which rows belong together.

Every phrasing depends on the neighbouring rows

Each of these phrasings asks about a group of rows that is defined by what the rows next to it look like. A user's 3-day streak is a streak because the 3 dates sit side by side with nothing missing between them. Remove the middle date and the same 2 remaining rows become 2 streaks of 1. That dependence on neighbours is the mark of the pattern: no single row carries enough information to say which group it belongs to, so a WHERE clause cannot find the group and a plain GROUP BY has nothing to group on.

The operator SQL does not have

SQL has no keyword for "consecutive". GROUP BY collapses rows that share a value in a column, and a table of dates has no column whose value is shared along a streak. The database sees 1, 2, 3, 7, 8, 12 as 6 numbers with no idea that the first 3 belong together. The whole pattern is the work of inventing that column: a value that is identical for every row in a run and different for rows in other runs, computed from the order of the rows, so that an ordinary GROUP BY can finish the job.

That is why the self-join and the recursive CTE feel natural and go wrong. Joining each login to the same user's previous day is an equi-join, which the engine runs as a hash or merge join in close to linear time, but it only flags the rows where a streak starts or stops. Turning those flags into groups needs a second step: an inequality self-join that compares each row with every earlier row of the same user, around 500,000 comparisons for a user with 1,000 logins, or a recursive walk that takes 1 iteration per day of the longest streak. Both return the right answer on the 5-row sample, and the sample never shows you the cost.

The sentence to say before typing

Say 1 sentence before you touch the keyboard, because the interviewer is scoring recognition first and syntax second. It names the pattern and the tool along with why the tool works, and it takes roughly 10 seconds to say:

The answer that stalls
  • Writes a recursive CTE that walks the table 1 row at a time
  • Writes a correlated subquery comparing each row to its neighbours
  • Looks for a built-in consecutive-run function that does not exist
  • Spends 10 minutes unable to express the grouping and runs out of time
The answer that gets scored
  • Names the pattern out loud before the first keystroke
  • Numbers each user's rows with ROW_NUMBER(), restarting per user and sorted by date
  • Computes value minus row number and groups by that difference
  • Gives the 2-sentence proof: lockstep inside a run, divergence at a gap

Where the same question lives at work

Gaps-and-islands queries sit under every product dashboard that touches sequential events: engagement streaks (consecutive days a user logged in), retention (unbroken weeks of activity), billing (contiguous months of an active subscription), inventory (continuous nights a room could be sold), fleet operations (consecutive days a vehicle was in service) and monitoring (unbroken stretches of a passing health check). That spread is why the pattern appears in entry-level data engineering loops even though the technique feels advanced: an engineer who cannot write it is locked out of a whole class of business questions.

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.