LAG / LEAD
Read a value from the previous (LAG) or next (LEAD) row within a partition, in a defined order. The backbone of row-over-row metrics: month-on-month revenue change, days-between-orders, session gap detection, cohort transitions.
Syntax #
sql
SELECT key_col, ts_col, metric,
LAG(metric) OVER (PARTITION BY key_col ORDER BY ts_col) AS prev_metric,
LEAD(metric) OVER (PARTITION BY key_col ORDER BY ts_col) AS next_metric
FROM table_name;Example #
Loading SQL editor...
Warning
The first row has no LAG and the last row has no LEAD, so both return NULL by default. Downstream math like total - prev_total becomes NULL, which SUM silently skips and AVG silently excludes from the denominator. Either use the third argument (LAG(total, 1, 0)) to provide a default, or COALESCE the result, but decide consciously — the "right" boundary value depends on the metric.