LAG & LEAD
LAG() and LEAD() are window functions that let a row reach across to a neighboring row in the same result set and pull in one of its values — without needing a self-join. LAG() looks backward to a previous row; LEAD() looks forward to a following row. Both operate within the ordering defined by the OVER clause's ORDER BY, and optionally within partitions defined by PARTITION BY.
LAG(expression, offset, default) OVER (PARTITION BY ... ORDER BY ...) LEAD(expression, offset, default) OVER (PARTITION BY ... ORDER BY ...)
expression — the column or expression to fetch from the neighboring row.
offset — how many rows back (LAG) or forward (LEAD) to look. Defaults to 1 if omitted.
default — the value to return when there is no such row (e.g. LAG on the very first row of a partition). Defaults to NULL if omitted.
Worked example: day-over-day change
Given a table of daily sales totals:
sale_date | revenue -----------+-------- 2024-01-01 | 1000 2024-01-02 | 1200 2024-01-03 | 1150 2024-01-04 | 1400
LAG() can pull in yesterday's revenue alongside today's row, making it trivial to compute the day-over-day change in a single query without a self-join:
SELECT sale_date, revenue, LAG(revenue, 1) OVER (ORDER BY sale_date) AS prev_day_revenue, revenue - LAG(revenue, 1) OVER (ORDER BY sale_date) AS change_vs_prev_day FROM daily_sales;
sale_date | revenue | prev_day_revenue | change_vs_prev_day -----------+---------+------------------+-------------------- 2024-01-01 | 1000 | NULL | NULL 2024-01-02 | 1200 | 1000 | 200 2024-01-03 | 1150 | 1200 | -50 2024-01-04 | 1400 | 1150 | 250
The first row has no previous day within the result set, so LAG() returns NULL there (and the subtraction propagates NULL as well). LEAD() works identically but looks the other direction — LEAD(revenue, 1) on 2024-01-01 would return 1200, the following day's value.
Supplying a default for boundary rows
Rather than leaving the boundary rows as NULL, pass a third argument to LAG()/LEAD() to supply a fallback value:
SELECT sale_date, revenue, LAG(revenue, 1, 0) OVER (ORDER BY sale_date) AS prev_day_revenue FROM daily_sales;
sale_date | revenue | prev_day_revenue -----------+---------+----------------- 2024-01-01 | 1000 | 0 2024-01-02 | 1200 | 1000 2024-01-03 | 1150 | 1200 2024-01-04 | 1400 | 1150
This is especially useful when the LAG/LEAD result feeds directly into arithmetic, since a stray NULL would otherwise silently turn the whole calculation NULL for that row.
Partitioning LAG/LEAD
Add PARTITION BY to compute the previous/next value independently within each group — for example, the previous month's revenue per store, without leaking one store's last row into another store's first row:
SELECT store_id, sale_month, revenue, LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_month) AS prev_month_revenue FROM monthly_store_sales;