LAG and LEAD in SQL Server: Real-World Examples

LAG (column, offset, default)  OVER (PARTITION BY ... ORDER BY ...) -> value from a PREVIOUS row
LEAD(column, offset, default)  OVER (PARTITION BY ... ORDER BY ...) -> value from a NEXT row

Used for: day-over-day / month-over-month change, time between events,
          detecting status changes, comparing with next value.

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

USE SqlLearningDb;
GO

Topic table: StockPrices (daily closing prices for 3 stocks)

DROP TABLE IF EXISTS dbo.StockPrices;

CREATE TABLE dbo.StockPrices
(
    Symbol     VARCHAR(10)   NOT NULL,
    TradeDate  DATE          NOT NULL,
    ClosePrice DECIMAL(10,2) NOT NULL,
    Volume     INT           NOT NULL,
    PRIMARY KEY (Symbol, TradeDate)
);

INSERT INTO dbo.StockPrices (Symbol, TradeDate, ClosePrice, Volume) VALUES
('INFY', '2025-06-02', 1520.50, 51000), ('INFY', '2025-06-03', 1534.20, 48000),
('INFY', '2025-06-04', 1528.00, 39000), ('INFY', '2025-06-05', 1550.75, 62000),
('INFY', '2025-06-06', 1561.10, 58000), ('INFY', '2025-06-09', 1549.90, 41000),
('INFY', '2025-06-10', 1572.30, 70000), ('INFY', '2025-06-11', 1580.00, 66000),
('TCS',  '2025-06-02', 3410.00, 22000), ('TCS',  '2025-06-03', 3398.40, 25000),
('TCS',  '2025-06-04', 3425.60, 27000), ('TCS',  '2025-06-05', 3440.10, 30000),
('TCS',  '2025-06-06', 3431.00, 21000), ('TCS',  '2025-06-09', 3460.80, 35000),
('TCS',  '2025-06-10', 3455.25, 19000), ('TCS',  '2025-06-11', 3489.90, 40000),
('WIPRO','2025-06-02',  265.40, 90000), ('WIPRO','2025-06-03',  262.10, 95000),
('WIPRO','2025-06-04',  259.80, 99000), ('WIPRO','2025-06-05',  261.00, 87000),
('WIPRO','2025-06-06',  266.70,120000), ('WIPRO','2025-06-09',  270.20,110000),
('WIPRO','2025-06-10',  268.90, 85000), ('WIPRO','2025-06-11',  272.40,105000);

(24 rows affected)

Topic table: TicketStatusLog (status history of support tickets)

DROP TABLE IF EXISTS dbo.TicketStatusLog;

CREATE TABLE dbo.TicketStatusLog
(
    LogID     INT IDENTITY(1,1) PRIMARY KEY,
    TicketID  INT          NOT NULL,
    Status    VARCHAR(20)  NOT NULL,
    ChangedAt DATETIME2(0) NOT NULL
);

INSERT INTO dbo.TicketStatusLog (TicketID, Status, ChangedAt) VALUES
(101, 'Open',        '2025-06-01 09:00'), (101, 'In Progress', '2025-06-01 11:30'),
(101, 'On Hold',     '2025-06-02 10:00'), (101, 'In Progress', '2025-06-03 09:15'),
(101, 'Resolved',    '2025-06-03 17:45'),
(102, 'Open',        '2025-06-02 14:00'), (102, 'In Progress', '2025-06-02 14:20'),
(102, 'Resolved',    '2025-06-02 16:00'),
(103, 'Open',        '2025-06-03 08:00'), (103, 'In Progress', '2025-06-04 12:00'),
(103, 'Reopened',    '2025-06-05 09:00'), (103, 'Resolved',    '2025-06-06 18:30');
GO

(12 rows affected)

Part A: Basic LAG and LEAD

SELECT Symbol, TradeDate, ClosePrice,
       LAG(ClosePrice)        OVER (PARTITION BY Symbol ORDER BY TradeDate) AS PrevClose,
       LEAD(ClosePrice)       OVER (PARTITION BY Symbol ORDER BY TradeDate) AS NextClose,
       LAG(ClosePrice, 2)     OVER (PARTITION BY Symbol ORDER BY TradeDate) AS Close2DaysAgo,
       LAG(ClosePrice, 1, 0)  OVER (PARTITION BY Symbol ORDER BY TradeDate) AS PrevCloseDefault0
FROM dbo.StockPrices
ORDER BY Symbol, TradeDate;

Part B: Day-over-day Change

SELECT Symbol, TradeDate, ClosePrice,
       ClosePrice - LAG(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate) AS ChangeAmt,
       CAST(100.0 * (ClosePrice - LAG(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate))
            / LAG(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate) AS DECIMAL(6,2)) AS ChangePct,
       CASE WHEN ClosePrice > LAG(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate) THEN 'UP'
            WHEN ClosePrice < LAG(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate) THEN 'DOWN'
            ELSE '-' END AS Direction
FROM dbo.StockPrices
ORDER BY Symbol, TradeDate;

Part C: Month-over-month and Year-over-year Revenue

Month-over-month growth (Sales table, 2025)

WITH Monthly AS
(
    SELECT DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1) AS SaleMonth,
           SUM(TotalAmount) AS Revenue
    FROM dbo.Sales
    GROUP BY DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1)
)
SELECT SaleMonth, Revenue,
       LAG(Revenue) OVER (ORDER BY SaleMonth) AS PrevMonthRevenue,
       CAST(100.0 * (Revenue - LAG(Revenue) OVER (ORDER BY SaleMonth))
            / NULLIF(LAG(Revenue) OVER (ORDER BY SaleMonth), 0) AS DECIMAL(8,2)) AS MoMGrowthPct
FROM Monthly
ORDER BY SaleMonth;

Same month last year (Orders table covers 2024 and 2025) -> LAG by 12

WITH Monthly AS
(
    SELECT DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) AS OrderMonth,
           SUM(oi.LineTotal) AS Revenue
    FROM dbo.Orders o JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
    GROUP BY DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1)
)
SELECT OrderMonth, Revenue,
       LAG(Revenue, 12) OVER (ORDER BY OrderMonth) AS SameMonthLastYear
FROM Monthly
ORDER BY OrderMonth;
-- NOTE: LAG(…,12) only works if NO month is missing. Safer: self join on DATEADD(YEAR,-1,month).

Part D: Time BETWEEN Events

Days between consecutive orders of each customer

SELECT CustomerID, OrderID, OrderDate,
       LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID) AS PrevOrderDate,
       DATEDIFF(DAY, LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID), OrderDate) AS DaysSincePrev
FROM dbo.Orders
ORDER BY CustomerID, OrderDate;

Average gap between orders per customer (who buys most often?)

WITH Gaps AS
(
    SELECT CustomerID,
           DATEDIFF(DAY, LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID), OrderDate) AS GapDays
    FROM dbo.Orders
)
SELECT CustomerID, COUNT(GapDays) + 1 AS Orders, AVG(GapDays * 1.0) AS AvgDaysBetweenOrders
FROM Gaps
GROUP BY CustomerID
HAVING COUNT(GapDays) > 0
ORDER BY AvgDaysBetweenOrders;

How long each ticket stayed in each status (use LEAD for the "end time")

SELECT TicketID, Status, ChangedAt AS StartedAt,
       LEAD(ChangedAt) OVER (PARTITION BY TicketID ORDER BY ChangedAt) AS EndedAt,
       DATEDIFF(MINUTE, ChangedAt,
                LEAD(ChangedAt) OVER (PARTITION BY TicketID ORDER BY ChangedAt)) / 60.0 AS HoursInStatus
FROM dbo.TicketStatusLog
ORDER BY TicketID, ChangedAt;

Find tickets that went Backward (Resolved/In Progress -> On Hold or Reopened)

SELECT TicketID, PrevStatus, Status, ChangedAt
FROM (
        SELECT TicketID, Status, ChangedAt,
               LAG(Status) OVER (PARTITION BY TicketID ORDER BY ChangedAt) AS PrevStatus
        FROM dbo.TicketStatusLog
     ) t
WHERE Status IN ('On Hold', 'Reopened');

Part E: FIRST_VALUE and LAST_VALUE

-- LAST_VALUE needs a full frame! The default frame stops at the CURRENT row.
SELECT Symbol, TradeDate, ClosePrice,
       FIRST_VALUE(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate)        AS FirstClose,
       LAST_VALUE(ClosePrice)  OVER (PARTITION BY Symbol ORDER BY TradeDate)        AS LastClose_WRONG,
       LAST_VALUE(ClosePrice)  OVER (PARTITION BY Symbol ORDER BY TradeDate
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastClose_CORRECT,
       CAST(100.0 * (ClosePrice - FIRST_VALUE(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate))
            / FIRST_VALUE(ClosePrice) OVER (PARTITION BY Symbol ORDER BY TradeDate) AS DECIMAL(6,2)) AS GainSinceStartPct
FROM dbo.StockPrices
ORDER BY Symbol, TradeDate;

Each employee vs the highest and lowest paid person in their department

SELECT DepartmentID, FirstName, Salary,
       FIRST_VALUE(FirstName) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS TopEarner,
       FIRST_VALUE(FirstName) OVER (PARTITION BY DepartmentID ORDER BY Salary ASC)  AS LowestEarner
FROM dbo.Employees
WHERE DepartmentID IS NOT NULL
ORDER BY DepartmentID, Salary DESC;

Practice Questions

1. For each stock find the day with the biggest single-day % increase.
2. Find customers who have not ordered for more than 90 days between two orders.
3. Show each employee and the next person hired after them (LEAD on HireDate).
4. For each region, compare each month's revenue with the NEXT month (LEAD).
5. Find stocks that went UP for 3 consecutive days.