gaps and islands

Sessionization tells apart candidates who reach for a self-join from those who know LAG plus a running sum. Below is the two-pass pattern that holds up across billions of events, along with the edge cases interviewers press on.

Consecutive-run problems (active streaks, uptime windows, price-stable periods) all boil down to one trick. Once the row-number difference clicks, they collapse to a window function and a group-by.

Overlapping subscription or session windows have to collapse into clean periods, and a naive self-join runs quadratic. The signal is the gaps-and-islands trick: a running max end-time that marks where each new island begins.