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

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:

• `` lets you grab the next row's value
• `` 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:

If we write:

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

We’ll get:

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:

1. Forgetting ORDER BY

   • LAG() and LEAD() require ORDER BY inside the OVER() clause. Without it? Undefined chaos.

2. 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!

3. Skipping PARTITION BY when needed

   • If you want comparisons per customer, product, or region — don’t forget to partition!

4. 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

-- 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:

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():

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

✅ Option 2: Wrap with COALESCE()

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

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

Or if you're doing something dynamic like this:

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

✨ Which Should You Use?

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.

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:

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:

🛠 Debugging Tips

• Use ROW_NUMBER() temporarily to see your row order.
• Expect NULLs on edge rows — they’re normal!

• Use COALESCE() or optional argument to fill NULLs if needed:

  COALESCE(LAG(x), 0)
  or 
  LAG(Salary, 1, 0)
  

↻ Related Topics

(You might also like: FIRST_VALUE, ROW_NUMBER, Trends with SUM + ROWS BETWEEN)