LAG()
SQL · Window Functions
Theory
LAG(column, offset, default) returns the value of column from a row that is offset rows before the current row, in the order set by the window ORDER BY. With the default offset of 1 it is simply 'the previous row's value'. The first row has no previous row, so LAG returns NULL there, or the default you supply as the third argument.
This is how you compare a row with the one before it without a self-join. For monthly sales ordered by month, revenue - LAG(revenue) OVER (ORDER BY month) is the month-on-month change, and dividing by LAG(revenue) gives growth percentage. Add PARTITION BY region so each region compares only with its own previous month and the first month of every region starts with NULL. LAG ignores the window frame; only PARTITION BY and ORDER BY matter.
Why we use it
Period-over-period comparison is everywhere: daily change in stock price, month-on-month revenue growth, time since a customer's previous order. LAG puts the previous value on the same row so the comparison is a simple subtraction.
When to use it
Use LAG when you need a value from an earlier row in a sequence. Use LEAD to look forward instead. For a value from the first row of the group, use FIRST_VALUE; for a total of earlier rows, use SUM() OVER.
How it works
- Rows split by PARTITION BY and sorted by ORDER BY
- Take the current row
- Step back 'offset' rows within the same partition
- Does that row exist? Yes: Return its column value. No: Return the default (NULL if none given).
- Use it, e.g. revenue - previous revenue
- Previous value added to each row
Syntax
LAG(column, offset, default) OVER (ORDER BY col)Example
SELECT name, salary,
LAG(salary, 1) OVER (ORDER BY id) AS prev_salary
FROM employees;Key points
- offset defaults to 1 row back
- Returns NULL (or the given default) when there is no prior row
- Use PARTITION BY so groups never borrow values from each other
- The window ORDER BY decides what 'previous' means
- Ignores frames; only partition and order matter