LAG and LEAD: comparing with the previous or next row
LAG(column) returns the value from the previous row and LEAD(column) the value from the next row, in the order set by OVER (… ORDER BY …). That lets a row be compared with its neighbour.
Why it matters
"How much did revenue change since last month?", "how many days between a customer's orders?", "did the price go up?" — each compares a row with the one before it. Without LAG, that means a tricky self join on "the previous date". With LAG it is one line.
The idea
LAG(revenue) OVER (ORDER BY month) looks one row back in month order. The first row has no previous row, so it gets NULL (or a default you give as the third argument: LAG(revenue, 1, 0)). LEAD looks forward instead. The second argument says how far: LAG(revenue, 12) is the same month last year in monthly data.
Add PARTITION BY customer_id and the looking-back stops at each customer's boundary, so one customer's first order never looks back into another customer's orders.
Reading a bank statement line by line with your finger on the line above. LAG is that line above; the difference between them is the change.
On a statement the line above is always the day before. LAG gives the previous row, not the previous day: if a month has no orders, it has no row, and LAG silently compares March with January. Fill the gaps first when it matters.
Syntax
LAG(column, offset, default) OVER (PARTITION BY g ORDER BY d)
LEAD(column, offset, default) OVER (PARTITION BY g ORDER BY d)How it works
- A CTE makes one row per month: January 445, February 210, March 138.
LAG(revenue) OVER (ORDER BY month): January has no previous row, so NULL; February gets 445; March gets 210.- revenue −
LAG(revenue)gives the change: NULL, −235, −72. - With PARTITION BY customer_id, each customer's first order gets NULL from LAG.
- LEAD works the same way, looking forward: the last row gets NULL.
Key points
- LAG = previous row's value; LEAD = next row's value, in the window's ORDER BY.
- The first row (LAG) or last row (LEAD) of each partition gets NULL, or the default you pass.
LAG(x, n)looks n rows back — 12 for "same month last year" in monthly data.- PARTITION BY keeps each group's comparisons inside the group.
- LAG looks at the previous row, not the previous date: fill missing dates first if gaps matter.
Common mistakes
- Forgetting PARTITION BY, so one customer's first order is compared with another customer's last.
- Assuming the previous row is the previous day or month when there are gaps.
- Dividing by LAG without guarding against NULL or zero.
Not true: In daily data, the previous row in a window is the previous day, even when days are missing.
In fact: LAG looks one row back, not one day back. If a day has no row, the previous row is an older day, so check for gaps before calling a change day-on-day.
In the interview
LAG and LEAD return a value from a preceding or following row within the window, in the order given by the OVER clause's ORDER BY. I use them for period-over-period changes — revenue minus LAG of revenue — and gaps between events per user with PARTITION BY user. I watch out for the first row returning NULL and for missing periods, since LAG goes to the previous row, which isn't necessarily the previous calendar month.
Part of Unit 5: Window functions: ranking, running totals and comparing rows in the free SQL course.