SQL Interview Prep · Lesson

The Row-Number Difference Trick

Subtracting ROW_NUMBER from a sequence to group consecutive values into islands.

The Row-Number Difference Trick is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Most Elegant Island Key

The row-number difference trick is the technique interviewers most want to see for islands of consecutive integers or dates. It produces the group key in a single subtraction, no LAG and no running sum required.

The whole idea: subtract a ROW_NUMBER from the value itself. For any run of consecutive values, both the value and the row number increase by exactly 1 each step, so their difference is constant across the entire run. That constant is your island key.

Why The Difference Stays Constant

Think about two adjacent rows in a consecutive run. Going from one to the next, the value increases by 1 and the row number increases by 1. Subtract them and the +1s cancel, so value - row_number does not change.

But the moment there is a gap, the value jumps by more than 1 while the row number still only climbs by 1. The difference shifts to a new constant. That shift is exactly what separates one island from the next.

Seeing It On Our Data

Recall the login days 1, 2, 3, 7, 8, 10. Let's lay out the row number and the difference side by side:

day 1, rn 1, diff 0
day 2, rn 2, diff 0
day 3, rn 3, diff 0
day 7, rn 4, diff 3
day 8, rn 5, diff 3
day 10, rn 6, diff 4

The diffs (0,0,0,3,3,4) perfectly partition the rows into the three islands. Same diff means same island.

SELECT
  day_no,
  ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;

Collapsing Into Islands

With the difference as the group key, the final query is the standard collapse. Wrap the difference in a CTE and GROUP BY it:

This returns the same three islands as before, but the SQL is shorter and clearer than the LAG plus running-sum version. For integer or evenly-stepped sequences, this is the answer to reach for first.

WITH keyed AS (
  SELECT
    day_no,
    day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
  FROM logins
)
SELECT
  MIN(day_no) AS start_day,
  MAX(day_no) AS end_day,
  COUNT(*)    AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;

The Catch: Values Must Step By One

The plain difference trick assumes the sequence increases by exactly 1 per step. That is true for dense integers and consecutive calendar days, but it breaks if your values step by some other fixed amount or if duplicates exist.

Even values 2,4,6,8 will look like gaps to a value-minus-rownumber subtraction.
Duplicate values throw off the alignment because the row number keeps climbing while the value does not.

Knowing this limitation, and how to repair it, is what separates a memorized trick from real understanding.

Fixing Fixed-Step Sequences

If values step by a known constant k instead of 1, normalize first: divide the value by k (or use value / k for integers) so each step becomes 1 again, then subtract the row number.

For example, for even numbers stepping by 2, use day_no / 2 - ROW_NUMBER(). The normalized value now climbs by 1 per consecutive item, restoring the constant-difference property.

SELECT
  val,
  (val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;

Applying It To Dates

Dates are the most common real form. Calendar dates do not subtract from a row number directly, so convert the date to a day count first. In Postgres, subtract a fixed anchor date to get an integer number of days, then apply the same trick.

Because consecutive calendar days differ by 1, the difference between the day count and the row number is once again constant within an island.

WITH keyed AS (
  SELECT
    login_date,
    (login_date - DATE '2000-01-01')
      - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
  FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*)        AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;

Cross-Dialect Date Differencing

The date-to-integer step varies by engine, and interviewers appreciate cross-dialect awareness:

Postgres: subtract a date literal: login_date - DATE '2000-01-01' yields an integer.
MySQL: use DATEDIFF(login_date, '2000-01-01').
SQL Server: use DATEDIFF(day, '2000-01-01', login_date).

An even slicker route on some engines: subtract ROW_NUMBER days directly from the date using interval arithmetic, then GROUP BY the resulting anchor date.

SELECT
  login_date,
  login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
               * INTERVAL '1 day') AS grp_date
FROM daily_logins;

Adding Per-Group Partitions

For per-user islands, partition the row number by the group column. Critically, the group key must then include the partition column too, because two different users can coincidentally produce the same difference value.

So GROUP BY both user_id and the computed difference. Forgetting the user_id in the final GROUP BY is a subtle bug interviewers love to catch.

WITH keyed AS (
  SELECT user_id, day_no,
    day_no - ROW_NUMBER()
      OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;

Trick vs LAG: Which To Use

Two solid techniques now live in your toolkit. Pick deliberately:

Row-number difference: shortest and cleanest for runs of evenly-stepped values (dense integers, consecutive dates). First choice when adjacency means 'differs by a constant'.
LAG plus running sum: more flexible when adjacency is not a fixed numeric step, for example 'same status as the previous row' or irregular custom rules.

State your choice and why in the interview; the reasoning impresses more than the syntax.

Handling Duplicates Defensively

If a value can repeat and you still want one island per consecutive run, deduplicate first with DISTINCT or a grouping step so the row number aligns one-to-one with values. Alternatively use DENSE_RANK instead of ROW_NUMBER so tied values share a rank.

Always ask the interviewer whether duplicates can occur; the right defense depends on whether duplicates should extend or be ignored within a run.

WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;

Quick Check

Make sure you understand why the trick works.

Recap: The Difference Trick

You now own the cleanest island key:

Key formula: value - ROW_NUMBER() OVER (ORDER BY value) is constant per consecutive run.
Collapse with GROUP BY the difference to get start, end, and length.
For fixed-step sequences, normalize (divide by the step) first.
For dates, convert to an integer day count via the dialect's diff function.
Per group: PARTITION BY the row number and include the group column in the final GROUP BY.
Guard against duplicates with DISTINCT or DENSE_RANK.

Next we flip the focus from islands to the empty spaces: finding gaps.

Frequently asked questions

Is the “The Row-Number Difference Trick” lesson free?

Yes — the full text of “The Row-Number Difference Trick” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “The Row-Number Difference Trick”?

Subtracting ROW_NUMBER from a sequence to group consecutive values into islands. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “The Row-Number Difference Trick” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep