Mastering LAG and LEAD in SQL Server

When analyzing data in SQL Server, you often need to look at values from previous or next rows — for example, to calculate trends, compare sales over time, or detect changes in a sequence.

Two powerful window functions, LAG() and LEAD(), make this incredibly easy.

---

What Are LAG and LEAD?

• LAG(): Returns data from a previous row relative to the current row in a result set.

• LEAD(): Returns data from a next row relative to the current row.

Both functions work with OVER() clauses and windowing to define how rows are ordered and compared.

---

Basic Syntax

LAG(column_name, offset, default_value) OVER (ORDER BY some_column)
LEAD(column_name, offset, default_value) OVER (ORDER BY some_column)

• column_name: The column whose value you want from the previous/next row.

• offset: How many rows behind/ahead to look (default is 1).

• default_value: Value returned if there is no previous/next row (optional).

---

Example Dataset

Let’s create a simple sales table:

CREATE TABLE Sales (
    Id INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
);

INSERT INTO Sales VALUES
(1, '2025-08-01', 100.00),
(2, '2025-08-02', 120.00),
(3, '2025-08-03', 90.00),
(4, '2025-08-04', 150.00);

---

Using LAG

SELECT 
    SaleDate,
    Amount,
    LAG(Amount, 1, 0) OVER (ORDER BY SaleDate) AS PreviousAmount
FROM Sales;

Result:

Here, LAG() lets us compare the current day’s sales with the previous day’s.

---

Using LEAD

SELECT 
    SaleDate,
    Amount,
    LEAD(Amount, 1, 0) OVER (ORDER BY SaleDate) AS NextAmount
FROM Sales;

Result:

Here, LEAD() gives us the next day’s sales for each record.

---

Practical Use Case: Sales Difference

We can combine LAG() to calculate day-over-day changes:

SELECT 
    SaleDate,
    Amount,
    Amount - LAG(Amount, 1, Amount) OVER (ORDER BY SaleDate) AS ChangeFromPreviousDay
FROM Sales;

Result:

---

Key Points

• LAG() is perfect for comparing with previous rows.

• LEAD() is ideal for comparing with next rows.

• Always use ORDER BY in your OVER() clause to define the row sequence.

• You can provide a default value to handle missing previous/next rows.

---

Conclusion
LAG() and LEAD() are powerful tools for analyzing trends, detecting changes, and comparing sequential data without writing complex self-joins. They make your SQL cleaner, faster, and easier to maintain.

---

I’m Morteza Jangjoo and “Explaining things I wish someone had explained to me”