LAG and LEAD in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Window Functions Introduction (OVER, PARTITION BY)
Builds toward Period-over-Period Analysis, Sessionization and Funnel Analysis Patterns
What are 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.
SELECT customer_id, ordered_at::date, total_amount, LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS prev_order FROM orders ORDER BY customer_id, ordered_at LIMIT 10
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.
What is the difference between LAG and LEAD?
LEAD works the same way in the other direction. If you need to look ahead rather than back:
LEAD(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS next_orderHow do you compare a row to the previous row in SQL?
Once you have both values in the same row, the difference is just arithmetic in the SELECT list:
total_amount - LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS order_changeHow do you look back more than one row with LAG?
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:
LAG(total_amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY ordered_at)Does the query ORDER BY affect what LAG returns?
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.
Practice LAG and LEAD in SQL
Brightlane's sales operations team is compiling an order history report. Every order should appear alongside the amount that customer spent on their immediately preceding purchase.
Write a query to return every order's ID, customer ID, order amount, and that customer's previous order amount, ordered chronologically within each customer.
Assumptions:
- A customer's previous order is the order with the largest
ordered_atstrictly before the current row'sordered_at, restricted to that customer. - For a customer's first order — where the customer has no preceding order on record — the previous-amount value is missing.
- The final result is sorted by
customer_idascending, then byordered_atascending within each customer.
Output:
- One row per order, with columns
id,customer_id,total_amount, andprev_order_amount. Sorted bycustomer_id, thenordered_at.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 LAG and LEAD practice problems
Write a query to return every order's ID, customer ID, order amount, and that customer's previous order amount, ordered chronologically within each customer.
Write a query to return every order's ID, customer ID, order amount, and that customer's next order amount, ordered chronologically within each customer.
Write a query to return every order's ID, order amount, and the preceding order amount in the global chronological sequence, sorted by ordered_at.
Write a query to return every order's ID, customer ID, order amount, and the difference between that order's amount and the same customer's immediately preceding order amount.
Write a query to return every order's ID, customer ID, order amount, and that customer's previous order amount.
Write a query to return every delivered order's ID, customer ID, amount, and that same customer's immediately preceding delivered-order amount.
Write a query to return every order's ID, customer ID, order amount, and the order amount from two purchases later for that customer.
Write a query to return every order's ID, customer ID, order amount, and that same customer's chronologically previous order amount. Sort the final result by customer_id ascending, then by total_amount descending within each customer.
Write a query to return every order's ID, customer ID, order amount, the difference between that order's amount and the customer's average order amount across all their orders, and that same customer's previous order amount.
Start learning to practice all 9 LAG and LEAD problems, with instant grading and mastery tracking.
Common questions about LAG and LEAD
What does LAG return for the very first row?
NULL, because there is no earlier row to read. LEAD does the same at the other end. Supply a third argument if you would rather have a default such as zero, which keeps later arithmetic from turning into NULL.
Does LAG skip over NULL values?
No. It returns whatever sits in the previous row, including a NULL. If you need the previous value that is actually populated, filter the NULLs out before the window runs or use a frame that ignores them.
Can LAG and LEAD appear in the same query?
Yes, and putting both on a row is a common shape. Having the previous value and the next value alongside the current one turns a comparison across three rows into ordinary arithmetic in the SELECT list.
