Why the Difference Trick Works

Concepts covered: Row Numbering, Complex Data Patterns, Grouping, Common Table Expressions, Min & Max

With the rows numbered inside each entity, the island key is 1 subtraction. Take the sequence value and subtract the row number. Along an unbroken run the value climbs by 1 per row and the row number climbs by 1 per row, so the 2 move in lockstep and their difference holds still. At a gap the value jumps by more than 1 while the row number still climbs by 1, so the difference jumps by the size of the gap and never returns to its old value, because both columns only ever increase.

SELECT
  n,
  ROW_NUMBER() OVER (ORDER BY n) AS rn,
  n - ROW_NUMBER() OVER (ORDER BY n) AS island_id
FROM sequence_data

Watch the 6 rows of sequence_data, which hold 1 through 3, then jump to 7 and 8, then to 12. Row numbers land as 1 through 6. n - rn gives 0 on each of the first 3 rows and 3 on the rows for 7 and 8; the row for 12 gets 6. That is 3 distinct differences and 3 islands. The gap from 3 to 7 skipped 3 values and the difference moved from 0 to 3; the gap from 8 to 12 skipped 3 more and it moved from 3 to 6. The size of each jump in the key equals the number of missing values, which is a useful check when you trace by hand.

The proof in 2 sentences

That paragraph is the rubric item. An interviewer asks "why does subtracting the row number group the runs?" on almost every loop where this pattern appears, and candidates who memorised the query without the argument stall here. The second sentence matters most: the difference can never come back to an earlier value, so 2 different runs never share a key within the same partition.

The whole query

-- Find runs of consecutive integers in a single column
WITH ranked AS (
  SELECT
    n,
    ROW_NUMBER() OVER (ORDER BY n) AS rn
  FROM sequence_data
),
grouped AS (
  SELECT
    n,
    n - rn AS island_id
  FROM ranked
)
SELECT
  island_id,
  MIN(n)   AS island_start,
  MAX(n)   AS island_end,
  COUNT(*) AS island_length
FROM grouped
GROUP BY island_id
ORDER BY island_start;

Read the CTEs top down and they name the steps: ranked numbers the rows, grouped computes the key, and the final SELECT collapses each key into 1 row with MIN for the start, MAX for the end and COUNT(*) for the length. Point at island_id when you trace it and say "this column is identical for every row in a run, which is what lets GROUP BY fold the run into 1 row". The output has as many rows as there are islands, 3 here, never as many as the input.

COUNT(*) and MAX(n) - MIN(n) + 1 both give 3 for the first island, and the interviewer may ask which you would use. On clean data they always agree, so pick COUNT(*) because it reads as "rows in the run". Neither one catches a duplicated value: in 1, 2, 2, 3 the second 2 takes its own row number, the differences come out 0, 0, -1, -1, and the run splits in 2, with an island from 1 to 2 overlapping one from 2 to 3. Count and span both say 2 for each. The defence is the SELECT DISTINCT from section 2, which leaves 1 row per value before the numbering starts.

The label means nothing on its own

The keys in the example (0, 3, 6) are neither run lengths nor run starts, and no consumer should ever see them: they only happen to stay constant along a run. Drop island_id from the final SELECT in production and keep it only while debugging. Two users can produce the same label for unrelated runs, since each user's row numbers restart at 1, which is why the GROUP BY always carries the entity column beside the key; the next lesson in this track works through that pairing, and here the rule is to never group by the key alone when there is more than 1 entity.

Volunteer the caveat about the label unprompted: it tells the interviewer you derived the trick rather than copied it.

The same trick on dates

SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_length
FROM (
  SELECT
    user_id,
    login_date,
    login_date - ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) * INTERVAL '1 day' AS island_key
  FROM logins
) keyed
GROUP BY user_id, island_key
ORDER BY user_id, streak_start;

A date minus an integer needs the integer turned into days, so the subtraction becomes login_date - rn * INTERVAL '1 day', and the key is a date that stays the same along a streak. Alice's logins from March 1 to March 3 carry row numbers 1 to 3, so every one of them gives February 29, while her March 5 with row number 4 gives March 1, a different key. The query returns a 3-day streak and a 1-day streak for her and a 2-day streak for Bob. Everything else about the query is identical, and the calendar details of the date form are the next lesson's subject.

The follow-up is always "now give me the longest one per user". Wrap the streaks in a CTE and take MAX(streak_length) grouped by user_id for the number alone; returning the dates of that streak means picking a row, and the tie between 2 equal streaks is the awkward case the next section sketches.

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.