SQL Tutorial
•
•
4 min read
LAG() and LEAD() in SQL – How to Think and Solve Questions Step by Step (From Noob to Pro)
LAG() and LEAD() in SQL – How to Think and Solve Questions Step by Step (From Noob to Pro)
Hey, I’m Gaurav.
When I first saw `LAG()` and `LEAD()` in an interview, I had no idea what they were.
The interviewer asked:
"Show each order amount along with the previous order amount for the same customer."
My noob brain immediately went into panic mode:
Brute Force / Noob Way (What most beginners do):
- Try to use self-join with complex date conditions
- Write messy subqueries
- Get confused with "previous" logic and end up with wrong results
- Eventually give up and say "I’ll do it in Python"
Sound familiar?
Today, I’m going to show you exactly how I learned to think about `LAG()` and `LEAD()` — from confused beginner to solving them confidently in interviews.
We’ll use our familiar `orders` and `users` tables. You can practice every question live here: [sqlpractice.in](https://sqlpractice.in)
Let’s break it down step by step.
First, Understand the Core Problem
Ranking and running totals were already tricky.
Now the interviewer wanted previous or next row values — without collapsing the data.
My initial thought was: “I need to look at the row before or after the current one.”
That’s exactly what `LAG()` and `LEAD()` do.
What are LAG() and LEAD()?
- `LAG(column)` → Gets the value from the previous row
- `LEAD(column)` → Gets the value from the next row
Both use the `OVER()` clause with `PARTITION BY` and `ORDER BY`.
Basic syntax:
```sql
LAG(column_name) OVER (PARTITION BY group_column ORDER BY sort_column)
LEAD(column_name) OVER (PARTITION BY group_column ORDER BY sort_column)
```
Example 1: Previous Order Amount (LAG)
Question: For each order, show the amount and the previous order amount for the same customer.
Noob approach:
- Self-join with `o2.ordered_at < o.ordered_at` and then `MAX()` or something complicated
Better thinking process:
1. I need the value from the previous row.
2. The "previous" should reset for every customer → `PARTITION BY user_id`
3. The order of rows should be by date → `ORDER BY ordered_at`
Correct Solution:
```sql
SELECT
o.id,
u.name AS customer,
o.total_price AS current_amount,
LAG(o.total_price) OVER (
PARTITION BY u.id
ORDER BY o.ordered_at
) AS previous_amount
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY u.id, o.ordered_at;
```
Practice Question 1 (Easy)
Show each order with its amount and the previous order amount for the same customer.
[Try it live →](https://sqlpractice.in/?question=lag-basic)
Example 2: Next Order Amount (LEAD)
Question: Show each order amount and the next order amount for the same customer.
Correct Solution:
```sql
SELECT
o.id,
u.name AS customer,
o.total_price AS current_amount,
LEAD(o.total_price) OVER (
PARTITION BY u.id
ORDER BY o.ordered_at
) AS next_amount
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY u.id, o.ordered_at;
```
Practice Question 2 (Medium)
Show the next order amount for each order.
[Try it live →](https://sqlpractice.in/?question=lead-basic)
Example 3: Month-over-Month Growth (Real Interview Question)
Question: Calculate the month-over-month growth in total sales.
Noob approach: Complicated self-join with date logic
Better thinking:
1. I need total sales per month.
2. Then I need the sales from the previous month.
3. Use `LAG()` on the aggregated result.
Correct Solution:
```sql
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', ordered_at) AS month,
SUM(total_price) AS total_sales
FROM orders
GROUP BY month
)
SELECT
month,
total_sales,
LAG(total_sales) OVER (ORDER BY month) AS previous_month_sales,
ROUND(
(total_sales - LAG(total_sales) OVER (ORDER BY month)) 100.0
/ LAG(total_sales) OVER (ORDER BY month),
2) AS growth_percentage
FROM monthly_sales
ORDER BY month;
```
Practice Question 3 (Hard)
Calculate month-over-month growth in total sales.
[Try it live →](https://sqlpractice.in/?question=lag-growth)
How to Approach Any LAG/LEAD Question (My Thinking Framework)
When you see a LAG or LEAD question, follow this mental checklist:
1. What value do I need? (previous or next)
2. Should it reset per group? → Use `PARTITION BY` (e.g., per customer, per department)
3. In what order? → Use `ORDER BY` (usually date or sequence column)
4. What should happen at the edges?
- First row → LAG returns NULL
- Last row → LEAD returns NULL
Pro tip: Always use `PARTITION BY` when the "previous/next" should reset for different groups.
Final Thoughts
I used to avoid LAG and LEAD questions because they looked scary.
Now I see them as one of the cleanest tools in SQL.
The key is not memorizing syntax — it’s understanding:
- “I need the value from the previous row”
- “I need to group by customer/department”
- “I need to order by date”
Go practice these LAG and LEAD questions on [sqlpractice.in](https://sqlpractice.in) — the live editor will show you instantly if your thinking is correct.
Next article: Types of Views in SQL
Keep practicing — one concept at a time!
Building sqlpractice.in to help everyone master SQL for interviews — completely free.