Sketching the Sequence by Hand

Before writing the query in the room, draw the sequence. Put the value in the first column and the row number in the second; the third holds their difference and the fourth a letter naming the island. With 9 rows this takes roughly 60 seconds. The sketch catches every mistake this pattern produces, because a wrong partition shows up as a letter that changes where it should not, and a wrong direction or a misread step does the same. It also gives you and the interviewer one picture to point at while you explain.

Read down the third column. It changes 3 times, so there are 4 islands: A from 1 to 3 with 3 rows, B from 7 to 8 with 2, C from 12 to 14 with 3, and D holding 20 alone. Two islands share the longest length, and the sketch has already answered a question the interviewer has not asked yet. Check the jumps as well: 0 to 3 for 3 missing values, 3 to 6 for 3 more, 6 to 11 for the 5 values between 14 and 20.

The awkward cases the sketch shows

• An island of 1 row: D above, and the first thing to look for when the output seems to have too many rows.
• A tie for longest: A and C both have 3 rows, so "the longest streak" has 2 answers.
• Every row alone: the difference changes on every row and the result returns 1 row per input row.
• An empty table: no rows in, 0 rows out, with no error, which is the correct result and worth saying.
• Numbering in the wrong direction: the difference changes on every row even though runs exist.

The tie is the one that costs offers. ORDER BY island_length DESC LIMIT 1 returns A or C depending on which the engine happens to place first, and an interviewer who built the example put the tie in on purpose. Ask "should ties at the longest length both come back, or do you want exactly 1 row?" If both, filter the runs to the ones whose length equals the maximum, as the query below does; if 1, add a deterministic tiebreak such as the earliest start to the ORDER BY before the LIMIT.

SELECT
  *
FROM runs
WHERE island_length = (
  SELECT
    MAX(island_length)
  FROM runs
)

runs here is the output of section 3's query over the 9 values above, 1 row per island. The scalar subquery finds that the largest length is 3, and WHERE keeps every run that long, so the runs starting at 1 and at 12 both come back while 7 to 8 and 20 drop out. With 1 run alone at the top, the same query returns 1 row, so it is correct in both cases without changing anything.

A 60-second routine

When the interviewer hands you a sample with 2 users, sketch each user separately, because the partition is what the sketch is checking. A sketch that numbers both users in 1 column reproduces the missing-partition bug by hand, and watching a letter change in the middle of a real streak on the whiteboard is the fastest way to show why the query needs PARTITION BY. If the sample has a user with 1 login, trace that user too: an island of 1 row is where most "too many rows" complaints come from, and it takes 1 line to show that the query is right and the question was underspecified.

The sketch also tells you what to type last. Once the letters are down, the aggregates write themselves: the smallest value under each letter is the start, the largest is the end, and the count of rows is the length. If the question asked for a minimum streak of 2, cross out D on the sketch and add the HAVING that does the same in the query. Everything the interviewer sees on the board matches a line in the SQL, which is what makes the trace convincing.

Do

• Say "gaps and islands" and the subtraction sentence before writing a line
• Pin down the sequence column and its step, and ask whether an island of 1 counts
• Number with ROW_NUMBER() partitioned by entity, ordered ascending by the sequence
• Trace a 6-to-9 row sketch by hand that includes a gap and a 1-row island
• Ask how ties for the longest run should be returned

Avoid

• Reach for a self-join or recursive CTE to walk neighbouring rows
• Number across all entities with 1 window and trust the result
• Show the n - rn label in the output as if it meant something
• Use LIMIT 1 for the longest run without a tiebreak or a question

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.