🧠 LAG() and LEAD(): Look Back and Ahead

So far, Xorthax has been calculating totals and averages across rows. But now he wants to compare rows directly.

“What happened before this?” “What happens next?”

That’s where LAG() and LEAD() come in.

🚀 LAG() and LEAD() + OVER()

LAG() and LEAD() let you access values from other rows without joining the table to itself.

LAG(column)  OVER (ORDER BY column)
LEAD(column) OVER (ORDER BY column)

• LAG() → looks at a previous row
• LEAD() → looks at a following row

📊 Example: Compare to Previous and Next Row

🧾 Sample Data

SELECT OrderDate, Amount,
       LAG(Amount)  OVER (ORDER BY OrderDate) AS PrevAmount,
       LEAD(Amount) OVER (ORDER BY OrderDate) AS NextAmount
FROM Orders;

Result:

Each row now includes values from the row before and after it.

🧠 What’s Happening Here?

ORDER BY OrderDate defines the sequence of rows. ORDER BY is required for any LAG() or LEAD() statement.

LAG() looks one row backward in that sequence.

LEAD() looks one row forward.

For the first row, there is no previous value, so LAG() returns null.

For the last row, there is no next value, so LEAD() returns null.

These functions let you compare rows without writing joins or subqueries.

🧪 Practice What You Just Learned

SELECT orderid, orderdate, totalamount, LAG(totalamount) OVER (ORDER BY orderdate) AS previous_amount FROM orders ORDER BY orderdate;

➡️ Next Step

Now that you can look at previous and next rows, you can start pulling specific values from a sequence.

Next, you’ll learn how to get the first value in a group.

Want to go deeper?