gaps and islands

Sessionization separates people who reach for a self-join from people who know LAG plus a running sum. Here is the two-pass pattern that scales to billions of events, plus the edge cases interviewers push on.

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

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