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

  1. A CTE makes one row per month: January 445, February 210, March 138.
  2. LAG(revenue) OVER (ORDER BY month): January has no previous row, so NULL; February gets 445; March gets 210.
  3. revenue − LAG(revenue) gives the change: NULL, −235, −72.
  4. With PARTITION BY customer_id, each customer's first order gets NULL from LAG.
  5. LEAD works the same way, looking forward: the last row gets NULL.

Key points

Common mistakes

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.