LAG and LEAD in SQL Server: Simple Examples

10a - LAG and LEAD  (VERY SIMPLE version)
---------------------------------------------------------------------
No setup needed. This file creates its own small table.

LAG = look at the PREVIOUS row LEAD = look at the NEXT row

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

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

IF DB_ID('SqlLearningDb') IS NULL
    CREATE DATABASE SqlLearningDb;
GO

USE SqlLearningDb;
GO

Small table: sales of one shop for 6 months

DROP TABLE IF EXISTS dbo.ShopSales;

CREATE TABLE dbo.ShopSales
(
    SaleMonth INT,          -- 1 = Jan, 2 = Feb ...
    MonthName VARCHAR(10),
    Amount    INT
);

INSERT INTO dbo.ShopSales (SaleMonth, MonthName, Amount) VALUES
(1, 'Jan', 1000),
(2, 'Feb', 1500),
(3, 'Mar', 1200),
(4, 'Apr', 1800),
(5, 'May', 1700),
(6, 'Jun', 2000);
GO

SELECT * FROM dbo.ShopSales;

(6 rows affected)

LAG: previous month's amount

Jan has no previous month, so it shows NULL

SELECT MonthName,
       Amount,
       LAG(Amount) OVER (ORDER BY SaleMonth) AS PreviousMonth
FROM dbo.ShopSales;

LEAD: next month's amount

Jun has no next month, so it shows NULL

SELECT MonthName,
       Amount,
       LEAD(Amount) OVER (ORDER BY SaleMonth) AS NextMonth
FROM dbo.ShopSales;

LAG and LEAD together

SELECT MonthName,
       LAG(Amount)  OVER (ORDER BY SaleMonth) AS PreviousMonth,
       Amount                                 AS ThisMonth,
       LEAD(Amount) OVER (ORDER BY SaleMonth) AS NextMonth
FROM dbo.ShopSales;

Show 0 instead of NULL (third value = default)

SELECT MonthName,
       Amount,
       LAG(Amount, 1, 0) OVER (ORDER BY SaleMonth) AS PreviousMonth
FROM dbo.ShopSales;

Go back 2 rows instead of 1 (second value = how many rows)

SELECT MonthName,
       Amount,
       LAG(Amount, 2) OVER (ORDER BY SaleMonth) AS TwoMonthsAgo
FROM dbo.ShopSales;

Difference from last month (most common use of LAG)

SELECT MonthName,
       Amount,
       LAG(Amount) OVER (ORDER BY SaleMonth)          AS PreviousMonth,
       Amount - LAG(Amount) OVER (ORDER BY SaleMonth) AS Difference
FROM dbo.ShopSales;

Did sales go Up or Down?

SELECT MonthName,
       Amount,
       CASE
           WHEN Amount > LAG(Amount) OVER (ORDER BY SaleMonth) THEN 'UP'
           WHEN Amount < LAG(Amount) OVER (ORDER BY SaleMonth) THEN 'DOWN'
           ELSE '-'
       END AS Trend
FROM dbo.ShopSales;

Practice Questions

1. Show each month with the NEXT month's name (use LEAD on MonthName).
2. Show how much sales will change from this month to next month (use LEAD).
3. Show the amount from 3 months ago.