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.
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)`.
Measure the strength of a linear relationship between two columns with `CORR(y, x)` and covariance with `COVAR_SAMP` / `COVAR_POP` in PostgreSQL.
Measure how spread out values are with `STDDEV_SAMP` / `VAR_SAMP` in MySQL, MariaDB, and PostgreSQL, or `STDEV` / `VAR` in SQL Server.
Measure how many unique users completed each stage of a multi-step flow using `COUNT(DISTINCT CASE WHEN event_name = '...' THEN user_id END)` per step.
Flag values that fall outside the interquartile fence `[Q1 − 1.5 × IQR, Q3 + 1.5 × IQR]` using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server.
Produce a row for every date in a range — even dates with no data — by generating a date spine and left-joining actual records onto it.
Identify the value that appears most often in a column using `GROUP BY` with `ORDER BY COUNT(*) DESC LIMIT 1`, or with `MODE() WITHIN GROUP` in PostgreSQL.
Compute the least-squares slope, intercept, and R² of a linear regression directly in SQL using `REGR_SLOPE(y, x)`, `REGR_INTERCEPT(y, x)`, and `REGR_R2(y, x)` in PostgreSQL.
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 ()`.