The Row-Number Difference Trick

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.

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.