DEV Community

Cover image for Mastering LAG and LEAD in SQL Server
Morteza Jangjoo
Morteza Jangjoo

Posted on

Mastering LAG and LEAD in SQL Server

#sqlwindowfunctions #databasetips #database #sqlserver

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)
Enter fullscreen mode Exit fullscreen mode
  • 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);
Enter fullscreen mode Exit fullscreen mode

Using LAG 

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

Result:

SaleDate Amount PreviousAmount
2025-08-01 100.00 0.00
2025-08-02 120.00 100.00
2025-08-03 90.00 120.00
2025-08-04 150.00 90.00

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;
Enter fullscreen mode Exit fullscreen mode

Result:

SaleDate Amount NextAmount
2025-08-01 100.00 120.00
2025-08-02 120.00 90.00
2025-08-03 90.00 150.00
2025-08-04 150.00 0.00

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;
Enter fullscreen mode Exit fullscreen mode

Result:

SaleDate Amount ChangeFromPreviousDay
2025-08-01 100.00 0.00
2025-08-02 120.00 20.00
2025-08-03 90.00 -30.00
2025-08-04 150.00 60.00

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”

Top comments (0)