Gaps and Islands

Gaps and islands is the SQL pattern for collapsing a sequence of rows into the contiguous runs (islands) and the breaks between them (gaps). The trick is a difference of two row numbers that stays constant inside a run, giving every row in the same island an identical group key you can then aggregate. It powers sessionization, login streaks, and contiguous date-range queries, and interviewers love it because the naive self-join answer is both slow and wrong on ties.