LAG and LEAD
Window functions that read a value from the previous (LAG) or next (LEAD) row, without a self-join.
Syntax
LAG(column [, offset [, default]]) OVER ([PARTITION BY …] ORDER BY …)LEAD(column [, offset [, default]]) OVER ([PARTITION BY …] ORDER BY …)
- offset is how many rows back or forward (default 1). default replaces the NULL at the edges.
Example
SELECT order_id, order_date, LAG(order_date) OVER (ORDER BY order_id) AS prev_date FROM orders ORDER BY order_id;
Each order with the date of the order before it; the first order has NULL.
Tables used: orders. Try it in a lesson below.
Common mistakes
- Without ORDER BY inside OVER the result depends on the row order and is not reliable.
- Add PARTITION BY when the comparison must stay inside a group, such as one customer.