What happened before this row?
A customer success rep is looking at repeat customers and asks a simple question: for a customer who orders more than once, is each new order bigger or smaller than their last one? Right now that means opening each customer’s order history and comparing rows by eye.
What happened before this row?
I want, for every order, the amount of that same customer's previous order — right there on the same row, so I can compare them directly.
You already have the vocabulary for the first half of that. Partition by customer, so each customer's history stays separate. What you're missing is a function that can reach into a neighboring row.
LAG() does exactly that: it pulls a value from an earlier row in the same window, based on whatever you put in ORDER BY. LAG(amount) means “the amount from one row back.” Its mirror image, LEAD(), reaches forward instead — “the value from one row ahead.”
Same building blocks as before: PARTITION BY keeps each customer’s orders in their own group, and ORDER BY inside OVER decides what “before” and “after” even mean — here, chronological order by placed_at.
What did this same customer’s last row look like?
Customer 5 has five orders on file — a good one to trace by hand:
SELECT customer_id, placed_at, amount,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS prev_amount
FROM orders
WHERE customer_id = 5
ORDER BY placed_at;| customer_id | placed_at | amount | prev_amount |
|---|---|---|---|
| 5 | 2023-05-01 | 34.5 | NULL |
| 5 | 2023-05-15 | 44.97 | 34.5 |
| 5 | 2023-06-20 | 27 | 44.97 |
| 5 | 2023-07-04 | 64 | 27 |
| 5 | 2023-08-11 | 79.99 | 64 |
The very first row has no prev_amount — it’s NULL, and that’s correct, not a bug. There genuinely is no order before customer 5’s first one. Same idea as an unmatched row after a LEFT JOIN: NULL is SQL’s way of saying “nothing there,” not an error.
From here it’s one more step to a “change from last time” column — just subtract:
SELECT customer_id, placed_at, amount,
amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS change_from_prev
FROM orders
WHERE customer_id = 5
ORDER BY placed_at;| customer_id | placed_at | amount | change_from_prev |
|---|---|---|---|
| 5 | 2023-05-01 | 34.5 | NULL |
| 5 | 2023-05-15 | 44.97 | 10.47 |
| 5 | 2023-06-20 | 27 | -17.97 |
| 5 | 2023-07-04 | 64 | 37 |
| 5 | 2023-08-11 | 79.99 | 15.99 |
That refunded order on 2023-06-20 shows up as a real dip — negative 17.97 — before recovering. Nothing about LAG knows or cares that the order was refunded; it just reports what the previous row looked like. The story is something you read into the numbers, not something SQL tells you directly.
LEAD is the same idea, pointed forward instead of backward:
SELECT customer_id, placed_at, amount,
LEAD(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS next_amount
FROM orders
WHERE customer_id = 5
ORDER BY placed_at;| customer_id | placed_at | amount | next_amount |
|---|---|---|---|
| 5 | 2023-05-01 | 34.5 | 44.97 |
| 5 | 2023-05-15 | 44.97 | 27 |
| 5 | 2023-06-20 | 27 | 64 |
| 5 | 2023-07-04 | 64 | 79.99 |
| 5 | 2023-08-11 | 79.99 | NULL |
Now it’s the last row that’s NULL — there’s no order after the most recent one. Every row shifts by exactly one position, in the opposite direction from LAG.
Running…
Across the whole table now — every customer gets their own independent LAG/LEAD chain. Customers with just one order get NULL on both sides, which makes sense: there’s nothing before or after their only order.
Try it: What happens if you drop PARTITION BY customer_id entirely?
Run your query to see results here.
Run it and check a row where the customer changes from the one before it. prev_amount now shows whichever order happened right before it chronologically, regardless of customer — so a customer’s very first order can end up with some other customer’s amount sitting next to it as its “previous” value. That’s exactly why PARTITION BY customer_id matters here: without it, “before this row” stops meaning “before this customer’s row” and starts meaning “before this row in the whole table,” which usually isn’t the question you meant to ask.
The billing team wants to spot customers whose orders are trending up. For every order, show customer_id, placed_at, amount, and the amount of that same customer’s next order, as next_amount. Order the result by customer_id, then placed_at.
Hint 1
You want to look forward from each row, not backward — which of LAG or LEAD does that?
Hint 2
PARTITION BY customer_id keeps each customer's own order history separate; ORDER BY placed_at inside OVER decides what 'next' means.
Hint 3
LEAD(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS next_amount
Solution
Running…
LAG and LEAD don’t aggregate anything — they just reach across to a neighboring row and hand back one of its values. The neighbor is defined entirely by your window: which partition you’re in, and what order you’ve put the rows in inside that partition. Change either one, and “before this row” changes meaning right along with it.
LAG reaches back to a previous row, LEAD reaches forward to the next one — both defined by ORDER BY inside OVER, both scoped by PARTITION BY. The first row in a partition has no LAG, and the last has no LEAD — that’s just NULL, the same "nothing there" you already know from joins.