gaps and islands

The hardest 'standard' SQL interview pattern, asked everywhere from Meta to Palantir. There's a three-line trick that turns consecutive runs into a GROUP BY key, once you've seen it, you can't unsee it.

Sessionization is gaps-and-islands with a twist, and it shows up in both SQL screens and Spark rounds at Databricks. The LAG-plus-running-SUM pattern here solves a whole family of interview questions.