Tag archive
Commands grouped around the same concept, pattern, or recurring problem.
Reference values from preceding or following rows within an ordered partition without writing a self-join.
Return a value from the first row of a window partition, useful for comparing each row against the group leader.
Group a stream of user events into sessions by labelling each event with a session ID that increments whenever the gap since the previous event exceeds a threshold.
Accumulate a column's values row by row using `SUM` as a window function.
Use `SUM(...) OVER ()` to compare each row's value to the total without collapsing the result set.
Pull the prior year's value into the current row with `LAG`, then compute the percentage change — a common pattern for revenue, signups, and other business KPIs.
Smooth time-series data by computing a rolling average over a fixed window of preceding rows using `AVG … OVER (ORDER BY … ROWS BETWEEN n PRECEDING AND CURRENT ROW)`.
Use `ROW_NUMBER()` to identify duplicate rows and delete every copy except the one you want to keep.
Create stable run groups without a primary key by subtracting two `ROW_NUMBER()` calculations over the same ordered events.
Rescale each row's value to the number of standard deviations it sits above or below the mean using `(value − AVG … OVER ()) / STDDEV_SAMP … OVER ()`.
Assign sequential integers to rows within a partition without collapsing the result set the way `GROUP BY` does.
Assign rank numbers to rows within a partition, controlling whether tied ranks leave gaps in the sequence.
Use `ROW_NUMBER()` to rank rows within each group and keep only the highest-ranked row.