LAG and LEAD in SQL

LAG and LEAD give each row access to a value from a different row in the same partition, without a join.

Picture this: your manager wants to see each customer's order amount alongside their previous order amount. You have one row per order. To compute the difference, you need this order's value and the prior order's value in the same row at the same time.

Before LAG, you'd write a self-join: join the orders table to itself on the same customer and an earlier date. It works, but it's brittle and gets slow on large tables. LAG solves it directly.

For each row, LAG reaches back one position in the partition — same customer, ordered by ordered_at — and returns that row's total_amount. The first order for each customer has no prior row, so it gets NULL. That's correct: there's no previous order, so the function returns the absence of one.

LEAD works the same way in the other direction. If you need to look ahead rather than back:

Once you have both values in the same row, the difference is just arithmetic in the SELECT list:

Controlling the offset

By default, LAG and LEAD look exactly one row away. Pass a second argument to change that: LAG(total_amount, 3) reaches back three rows. A third argument sets the fallback when there is no row at that offset. Instead of NULL for the first row, you can return 0:

The one thing that trips people up

The ORDER BY inside OVER is completely separate from the ORDER BY at the end of your query.

The ORDER BY inside OVER tells LAG and LEAD what "previous" and "next" mean — it defines which row to look at. The ORDER BY at the query level controls how your results are displayed. They are independent instructions.

You can order your output by one column while the window function orders its partition by another. If you sort results by revenue descending but the window ORDER BY is ascending by month, LAG still reads positions based on its own ORDER BY, not the output order. The rows might look sorted differently on screen, but the values LAG returns reflect the month sequence, not the display sequence. This mismatch produces results that look plausible but are wrong.