👀 Look Ahead, Look Behind: Mastering LEAD() and LAG() in SQL

This tutorial covers how to use LEAD() and LAG() in SQL to access previous or next row values. These window functions let you compare rows without writing self-joins—ideal for tracking changes, calculating deltas, or spotting trends.

Ever wish SQL could just tell you what came before* or *after* a row — without doing a self-join gymnastics routine?*

Enter LEAD() and LAG() — two of Xorthax's favorite tools for peeking into adjacent rows while keeping your query elegant.

🧐 What Are LEAD() and LAG()?

In simple terms:

LEAD() lets you grab the next row's value
LAG() grabs the previous one

They work inside a window, meaning they operate over a group of rows — without collapsing them like GROUP BY.

Imagine this:

OrderID	CustomerID	OrderDate	Amount
101	A1	2024-01-01	100
102	A1	2024-01-03	120
103	A1	2024-01-05	90

If we write:

SQL example: Use LAG() to retrieve the previous order amount for each customer, ordered by date.

LAG(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)

We’ll get:

OrderID	Amount	PrevAmount
101	100	NULL
102	120	100
103	90	120

Magical? Close. It’s just window functions being awesome.

🔍 When Should You Use LEAD or LAG?

Use these functions any time you want to:

Compare rows in sequence
Calculate differences or changes over time
Find trends like “what changed since last week?”
Predict next values, e.g. upcoming appointments, stockouts, or renewals

🧠 Look for These Keywords in Problems

When a question includes any of the following, LEAD or LAG should be top of mind:

"Previous" or "Next"
"Compare to last..."
"Find the change"
"Trend", "sequence", "shift"
"Difference between rows"

In your SQL muscle memory, that’s a LAG/LEAD flag.

⚠️ Common Pitfalls to Avoid

Even Xorthax got tripped up once or twice. Here are mistakes to dodge:

Forgetting ORDER BY
- LAG() and LEAD() require ORDER BY inside the OVER() clause. Without it? Undefined chaos.
Misunderstanding NULLs
- First row of LAG() and last row of LEAD() will return NULL by default. That’s expected — nothing to look at before or after!
Skipping PARTITION BY when needed
- If you want comparisons per customer, product, or region — don’t forget to partition!

Wrong offset

Second argument controls how far forward/back you look.

LAG(Amount, 2) → 2 rows behind
LEAD(Date, 3) → 3 rows ahead

🧱 Core Syntax

SQL example: Use LAG() to get the previous order amount by customer, and LEAD() to find the next delivery date by product, ordered by order date.

-- Get previous order amount
LAG(TotalAmount) OVER (
  PARTITION BY CustomerID
  ORDER BY OrderDate
) AS PrevAmount

-- Get next delivery date
LEAD(DeliveryDate) OVER (
  PARTITION BY ProductID
  ORDER BY OrderDate
) AS NextDelivery

🚯 Handling NULLs in LEAD/LAG

By default, LAG() and LEAD() return NULL when there’s no previous or next row. But what if you want to fill that in with something useful — like 0, 'N/A', or "no change"?

You’ve got two main options:

✅ Option 1: Built-in Default Value (3rd Argument)

SQL lets you set a default value right inside the function:

SQL example: Use the third argument in LAG() to return 0 when no previous value exists.

LAG(Salary, 1, 0)

This means:

Look 1 row back
If no row exists (e.g., first row), return 0 instead of NULL

Same applies to LEAD():

SQL example: Use LEAD() with a default fallback date to fill in when no next delivery exists.

LEAD(DeliveryDate, 1, '2999-12-31')

✅ Option 2: Wrap with COALESCE()

This gives you even more flexibility — especially when chaining or nesting:

SQL example: Wrap LAG() with COALESCE() to replace NULL with 0.

COALESCE(LAG(Amount) OVER (...), 0)

Or if you're doing something dynamic like this:

SQL example: Use COALESCE() to fall back to current salary if no prior salary exists within a department.

COALESCE(
  LAG(Salary) OVER (PARTITION BY Department ORDER BY HireDate),
  Salary -- fallback to current salary if none before
)

✨ Which Should You Use?

Situation	Use This
Simple fallback	`LAG(x, 1, fallback)`
You want clarity + readability	`LAG(x, 1, fallback)`
Fallback depends on logic	`COALESCE(LAG(...), x)`
You're chaining or nesting	`COALESCE(...)`

Both are correct and efficient. Pick based on readability and use case.

🧪 Gotchas on Ties and Missing Rows

Ties in ORDER BY? SQL doesn't break them unless you do. Add a tiebreaker column if needed.
Dates with gaps? LAG won’t fill them in — it just looks at row position, not calendar gaps.

❌ Frame Clauses Not Allowed

Unlike SUM() or AVG(), these functions don’t use ROWS BETWEEN. Why? Because they just fetch a specific row ahead or behind — not a range.

🔗 Chaining with CTEs

Sometimes, you’ll want to calculate with LAG/LEAD, then filter on it. That’s where CTEs come in handy.

SQL example: Use a CTE to calculate the previous order amount with LAG(), then filter to find orders where the amount decreased compared to the prior order.

WITH Ordered AS (
  SELECT
    CustomerID,
    OrderID,
    TotalAmount,
    LAG(TotalAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PrevAmount
  FROM Orders
)
SELECT *
FROM Ordered
WHERE TotalAmount < PrevAmount;

This shows all orders where the customer spent less than the time before.

🌟 Bonus: Combine Multiple Functions

You’re not limited to one! This is totally valid:

SQL example: Use LAG() and LEAD() together to compare each row’s value to the previous and next row within the window.

SELECT
  OrderID,
  TotalAmount,
  LAG(TotalAmount) OVER (...) AS Prev,
  LEAD(TotalAmount) OVER (...) AS Next

You can also combine with ROW_NUMBER() to get order positions or track gaps.

🤔 Is This a Window Function... or an Aggregate?

Let’s be clear:

Feature	Window Function	Aggregate (GROUP BY)
Keeps every row	✅	❌
Can look ahead/behind	✅ (`LEAD()`, `LAG()`)	❌
Collapses to one row/group	❌	✅

🛠 Debugging Tips

Use ROW_NUMBER() temporarily to see your row order.
Expect NULLs on edge rows — they’re normal!
Use COALESCE() or the optional argument to fill NULLs if needed:
SQL example: Fill missing values from LAG() using COALESCE() or the third argument to return 0 instead of NULL.
```
COALESCE(LAG(x), 0)
or 
LAG(Salary, 1, 0)
```

🧪 Practice Problems by Concept