Window functions unlock a remarkable range of analytical patterns. Sessionization — splitting a user's event stream into sessions based on a 30-minute inactivity gap — is achieved by summing a flag (1 when the gap from LAG exceeds 30 minutes, else 0) over the partition ordered by timestamp, which increments a session_id at each new session. Gaps and islands problems, where consecutive dates form a streak that should be grouped together, are solved by subtracting ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) * INTERVAL '1 day' from the date — rows in the same streak produce the same result. Cohort retention combines window aggregations with grouping, normalizing COUNT(DISTINCT user_id) over cohort and period by the first-period count retrieved via FIRST_VALUE.
Statistical measures are easily computed per partition. Z-scores use (x - AVG(x) OVER (...)) / STDDEV(x) OVER (...), with NULLIF guarding against zero standard deviation. Coefficient of variation divides STDDEV by AVG within the partition. Median per group uses PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER (PARTITION BY g) in PostgreSQL and BigQuery; SQL Server requires an OVER clause even for whole-table percentiles. Mode per group is found by aggregating counts per value, then ranking by count with ROW_NUMBER. Outlier detection via IQR computes Q1 and Q3 with PERCENTILE_CONT and flags rows beyond 1.5*IQR from the quartiles, while 3-sigma price spike detection compares current values to AVG ± 3*STDDEV over a trailing window.
Some calculations resist pure window expressions. Exponential moving averages require a recursive CTE because each EMA value depends on the previously computed EMA, with the recursive shape anchoring the first row and applying 0.3 * t.x + 0.7 * e.ema for subsequent rows. Cumulative distinct counts are likewise not directly expressible and need the first-occurrence-flag workaround. Time-weighted averages combine LAG on timestamps for interval lengths multiplied by values and summed, which is feasible but non-trivial. Sliding range frames with multiple intervals in one query are not supported, requiring either separate window calculations or self-joins on shifted timestamps. Lateral joins fill the gap when "the previous row matching a complex condition" cannot be expressed as a fixed offset, joining each row to a per-row subquery that selects the desired match.