SQL Server LEAD, LAG, FIRST_VALUE and LAST_VALUE for Time-Series Analysis (2026)

1. Offset Functions at a Glance

SQL Server's offset window functions let you reach into a different row of the same result set — earlier or later in the ordering — and pull a column value back into the current row. Introduced in SQL Server 2012 alongside the rest of the window function family, they eliminate the most common reason developers write self-joins on date columns.

There are four functions in this family:

General Syntax

-- LAG / LEAD
LAG  ( scalar_expression [, offset [, default]] )
    OVER ( [PARTITION BY partition_cols] ORDER BY sort_cols )

LEAD ( scalar_expression [, offset [, default]] )
    OVER ( [PARTITION BY partition_cols] ORDER BY sort_cols )

-- FIRST_VALUE / LAST_VALUE
FIRST_VALUE ( scalar_expression ) [IGNORE NULLS | RESPECT NULLS]
    OVER ( [PARTITION BY partition_cols] ORDER BY sort_cols
           [ROWS BETWEEN frame_start AND frame_end] )

LAST_VALUE  ( scalar_expression ) [IGNORE NULLS | RESPECT NULLS]
    OVER ( [PARTITION BY partition_cols] ORDER BY sort_cols
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

Key parameters for LAG and LEAD:

• offset — integer number of rows to jump (default 1). Must be a non-negative integer literal or variable.
• default — value returned when the offset falls outside the partition boundary (the first row has no prior row for LAG; the last row has no next row for LEAD). Defaults to NULL if omitted.

2. LAG(): Accessing a Prior Row Value

LAG() returns the value of an expression from a row that is N positions before the current row within the same partition and ordering. It is the idiomatic replacement for a self-join like t1 JOIN t2 ON t2.Period = t1.Period - 1.

Minimal Example

SELECT
    SaleDate,
    Revenue,
    LAG(Revenue) OVER (ORDER BY SaleDate) AS PrevRevenue
FROM dbo.MonthlySales;

For the first row (oldest date), PrevRevenue is NULL because there is no prior row. Supply a default to make the output cleaner:

LAG(Revenue, 1, 0) OVER (ORDER BY SaleDate) AS PrevRevenue

Now the oldest row shows 0 instead of NULL, which prevents division-by-zero errors in percentage-change calculations.

3. LAG Use Case 1 — Month-over-Month Revenue Change

One of the most common business requests is comparing this month's revenue to last month's — both as an absolute difference and as a percentage change. With LAG() this is a single query pass.

SELECT
    SaleYear,
    SaleMonth,
    Revenue,
    LAG(Revenue, 1, 0)
        OVER (ORDER BY SaleYear, SaleMonth)        AS PrevMonthRevenue,

    Revenue
    - LAG(Revenue, 1, 0)
          OVER (ORDER BY SaleYear, SaleMonth)      AS AbsoluteChange,

    CASE
        WHEN LAG(Revenue, 1, NULL)
                 OVER (ORDER BY SaleYear, SaleMonth) IS NULL
             THEN NULL
        ELSE ROUND(
                 100.0 * (Revenue
                          - LAG(Revenue, 1, 0)
                                OVER (ORDER BY SaleYear, SaleMonth))
                 / NULLIF(LAG(Revenue, 1, 0)
                              OVER (ORDER BY SaleYear, SaleMonth), 0)
             , 2)
    END                                            AS PctChange
FROM dbo.MonthlySales
ORDER BY SaleYear, SaleMonth;

Partitioned by Region

Add PARTITION BY RegionID to get independent month-over-month chains for each region without writing separate queries:

SELECT
    RegionID,
    SaleYear,
    SaleMonth,
    Revenue,
    LAG(Revenue, 1, 0)
        OVER (PARTITION BY RegionID
              ORDER BY SaleYear, SaleMonth) AS PrevRevenue,
    Revenue
    - LAG(Revenue, 1, 0)
          OVER (PARTITION BY RegionID
                ORDER BY SaleYear, SaleMonth) AS MoMChange
FROM dbo.RegionalMonthlySales
ORDER BY RegionID, SaleYear, SaleMonth;

4. LAG Use Case 2 — Detecting Gaps in Sequences

Any sequential identifier (order numbers, invoice IDs, ticket numbers) should increment by exactly 1. A gap means records were deleted, failed to insert, or a numbering bug occurred. LAG() identifies these gaps in one pass.

WITH OrderSequence AS (
    SELECT
        OrderID,
        LAG(OrderID, 1, NULL)
            OVER (ORDER BY OrderID) AS PrevOrderID
    FROM dbo.Orders
)
SELECT
    PrevOrderID      AS GapStartsAfter,
    OrderID          AS GapEndsBefore,
    OrderID - PrevOrderID - 1 AS MissingCount
FROM OrderSequence
WHERE OrderID - PrevOrderID > 1
ORDER BY PrevOrderID;

This returns one row per gap, showing exactly how many order numbers are missing and between which IDs the gap falls. For example, if PrevOrderID = 1005 and OrderID = 1009, then order numbers 1006, 1007, and 1008 are missing (MissingCount = 3).

5. LAG Use Case 3 — Session Duration from Event Log

Application event logs record a timestamp each time a user performs an action. There is often no explicit session-end event — the end of a session is implied by the start of the next. LAG() calculates the time between consecutive events per user.

SELECT
    UserID,
    EventTime,
    EventType,

    -- time since the user's previous event
    DATEDIFF(
        SECOND,
        LAG(EventTime, 1, NULL)
            OVER (PARTITION BY UserID ORDER BY EventTime),
        EventTime
    ) AS SecondsSincePrevEvent,

    -- flag rows where idle gap > 30 minutes — new session boundary
    CASE
        WHEN DATEDIFF(
                 MINUTE,
                 LAG(EventTime, 1, NULL)
                     OVER (PARTITION BY UserID ORDER BY EventTime),
                 EventTime) > 30
          OR LAG(EventTime, 1, NULL)
                 OVER (PARTITION BY UserID ORDER BY EventTime) IS NULL
        THEN 1
        ELSE 0
    END AS IsSessionStart

FROM dbo.AppEvents
ORDER BY UserID, EventTime;

A downstream SUM(IsSessionStart) OVER (PARTITION BY UserID ORDER BY EventTime) on this result produces a session number column that increments each time a 30-minute gap is detected — enabling full session-level aggregation without any procedural code.

6. LEAD(): Accessing the Next Row Value

LEAD() is the forward-looking mirror image of LAG(). It returns the value from a row N positions after the current row within the partition. The syntax is identical; only the direction changes.

SELECT
    CustomerID,
    OrderDate,
    OrderTotal,
    LEAD(OrderDate, 1, NULL)
        OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate,
    LEAD(OrderTotal, 1, NULL)
        OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderTotal
FROM dbo.Orders
ORDER BY CustomerID, OrderDate;

For the most recent order per customer, NextOrderDate is NULL. Supply a default value (e.g., GETDATE() or a far-future date) if downstream logic needs a non-null value.

7. LEAD Use Case 1 — Days Until Next Order per Customer

Understanding a customer's repurchase cadence — how many days typically pass between orders — is foundational for loyalty and email timing models.

SELECT
    CustomerID,
    OrderID,
    OrderDate,
    LEAD(OrderDate, 1, NULL)
        OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate,

    DATEDIFF(
        DAY,
        OrderDate,
        LEAD(OrderDate, 1, NULL)
            OVER (PARTITION BY CustomerID ORDER BY OrderDate)
    ) AS DaysToNextOrder

FROM dbo.Orders
ORDER BY CustomerID, OrderDate;

To get the average repurchase gap per customer, wrap this in a CTE and aggregate:

WITH OrderGaps AS (
    SELECT
        CustomerID,
        DATEDIFF(
            DAY,
            OrderDate,
            LEAD(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
        ) AS DaysToNext
    FROM dbo.Orders
)
SELECT
    CustomerID,
    COUNT(*)           AS OrderCount,
    AVG(DaysToNext)    AS AvgDaysBetweenOrders,
    MIN(DaysToNext)    AS MinGap,
    MAX(DaysToNext)    AS MaxGap
FROM OrderGaps
WHERE DaysToNext IS NOT NULL       -- exclude last order per customer
GROUP BY CustomerID
ORDER BY AvgDaysBetweenOrders;

8. LEAD Use Case 2 — Detecting Customer Churn

A customer who has not placed an order within a defined window (e.g., 90 days) after their last known order is classified as churned. LEAD() makes this determination row-by-row — you flag each order where the next order is either missing (last order ever) or falls outside the acceptable re-order window.

WITH OrderedPurchases AS (
    SELECT
        CustomerID,
        OrderDate,
        LEAD(OrderDate, 1, NULL)
            OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate
    FROM dbo.Orders
)
SELECT
    CustomerID,
    OrderDate      AS LastOrderDate,
    NextOrderDate,
    CASE
        WHEN NextOrderDate IS NULL
          AND DATEDIFF(DAY, OrderDate, GETDATE()) > 90
        THEN 'Churned'
        WHEN DATEDIFF(DAY, OrderDate, NextOrderDate) > 90
        THEN 'At Risk'
        ELSE 'Active'
    END AS ChurnStatus
FROM OrderedPurchases
-- only show the most recent order per customer
WHERE NextOrderDate IS NULL
   OR DATEDIFF(DAY, OrderDate, NextOrderDate) > 90
ORDER BY CustomerID;

9. LEAD Use Case 3 — Detecting Overlapping Event Intervals

Calendars, resource bookings, and shift schedules must not overlap. LEAD() compares the start of the next booking to the end of the current one to flag conflicts — a task that normally requires a self-join.

WITH BookingSequence AS (
    SELECT
        ResourceID,
        BookingID,
        StartTime,
        EndTime,
        LEAD(StartTime, 1, NULL)
            OVER (PARTITION BY ResourceID ORDER BY StartTime) AS NextStart,
        LEAD(BookingID, 1, NULL)
            OVER (PARTITION BY ResourceID ORDER BY StartTime) AS NextBookingID
    FROM dbo.ResourceBookings
)
SELECT
    ResourceID,
    BookingID            AS CurrentBooking,
    StartTime,
    EndTime,
    NextBookingID        AS ConflictingBooking,
    NextStart            AS ConflictStartTime
FROM BookingSequence
WHERE NextStart IS NOT NULL
  AND NextStart < EndTime          -- next booking starts before current ends
ORDER BY ResourceID, StartTime;

Each returned row identifies a pair of conflicting bookings on the same resource. Feed this into an alert, a UI badge, or a deduplication workflow.

10. FIRST_VALUE(): Anchoring to the Window Start

FIRST_VALUE() returns the value from the first row in the current window frame. Its most common pattern is retrieving a baseline value — the first price, the first event date, the opening balance — and carrying it forward on every subsequent row in the partition.

-- For each product, show the first-ever recorded price
-- alongside each subsequent price entry
SELECT
    ProductID,
    PriceDate,
    ListPrice,
    FIRST_VALUE(ListPrice)
        OVER (PARTITION BY ProductID ORDER BY PriceDate
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OriginalPrice,
    ListPrice
    - FIRST_VALUE(ListPrice)
          OVER (PARTITION BY ProductID ORDER BY PriceDate
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                                                               AS PriceIncrease
FROM dbo.ProductPriceHistory
ORDER BY ProductID, PriceDate;

Finding the Earliest Event Date per Partition

SELECT
    CustomerID,
    OrderDate,
    OrderTotal,
    FIRST_VALUE(OrderDate)
        OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS FirstOrderDate,
    DATEDIFF(DAY,
        FIRST_VALUE(OrderDate)
            OVER (PARTITION BY CustomerID ORDER BY OrderDate),
        OrderDate) AS DaysSinceFirstOrder
FROM dbo.Orders
ORDER BY CustomerID, OrderDate;

11. LAST_VALUE(): The Common Frame Trap

LAST_VALUE() is the most frequently misused offset function because its default window frame does not extend to the end of the partition. When you write ORDER BY col inside OVER(), SQL Server defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That means LAST_VALUE always returns the current row's own value — not the last row in the partition.

Wrong (returns current row, not partition end)

-- BUG: default frame stops at current row
SELECT
    ProductID,
    PriceDate,
    ListPrice,
    LAST_VALUE(ListPrice)
        OVER (PARTITION BY ProductID ORDER BY PriceDate) AS LatestPrice  -- WRONG
FROM dbo.ProductPriceHistory;

Correct (explicit ROWS frame extends to partition end)

-- FIX: extend the frame to the last row in the partition
SELECT
    ProductID,
    PriceDate,
    ListPrice,
    LAST_VALUE(ListPrice)
        OVER (PARTITION BY ProductID ORDER BY PriceDate
              ROWS BETWEEN UNBOUNDED PRECEDING
                       AND UNBOUNDED FOLLOWING) AS LatestPrice  -- CORRECT
FROM dbo.ProductPriceHistory
ORDER BY ProductID, PriceDate;

12. Combining LAG + LEAD: Island and Gap Detection

The classic islands and gaps problem asks: given a set of dates (or integers), identify groups of consecutive values (islands) and the spaces between them (gaps). Using both LAG and LEAD together lets you label island boundaries in one CTE pass.

-- Identify consecutive date sequences (islands) in a login log
WITH LoginDates AS (
    SELECT DISTINCT
        UserID,
        CAST(LoginTime AS DATE) AS LoginDate
    FROM dbo.UserLogins
),
Boundaries AS (
    SELECT
        UserID,
        LoginDate,
        -- is this date the START of an island?
        CASE
            WHEN DATEDIFF(DAY,
                     LAG(LoginDate, 1, NULL)
                         OVER (PARTITION BY UserID ORDER BY LoginDate),
                     LoginDate) > 1
              OR LAG(LoginDate, 1, NULL)
                     OVER (PARTITION BY UserID ORDER BY LoginDate) IS NULL
            THEN 1 ELSE 0
        END AS IsIslandStart,
        -- is this date the END of an island?
        CASE
            WHEN DATEDIFF(DAY,
                     LoginDate,
                     LEAD(LoginDate, 1, NULL)
                         OVER (PARTITION BY UserID ORDER BY LoginDate)) > 1
              OR LEAD(LoginDate, 1, NULL)
                     OVER (PARTITION BY UserID ORDER BY LoginDate) IS NULL
            THEN 1 ELSE 0
        END AS IsIslandEnd
    FROM LoginDates
),
Islands AS (
    SELECT
        UserID,
        LoginDate,
        SUM(IsIslandStart)
            OVER (PARTITION BY UserID ORDER BY LoginDate
                  ROWS UNBOUNDED PRECEDING) AS IslandID
    FROM Boundaries
)
SELECT
    UserID,
    IslandID,
    MIN(LoginDate) AS StreakStart,
    MAX(LoginDate) AS StreakEnd,
    DATEDIFF(DAY, MIN(LoginDate), MAX(LoginDate)) + 1 AS StreakLengthDays
FROM Islands
GROUP BY UserID, IslandID
ORDER BY UserID, StreakStart;

This returns each unbroken login streak per user with its start date, end date, and length in days — the foundation for engagement badges, streak leaderboards, and retention cohort analysis.

13. Multi-Offset LAG: Comparing to 3 and 12 Months Ago

Nothing prevents you from calling LAG() multiple times in the same SELECT with different offset values. This is the T-SQL way to compare a metric to multiple historical periods in a single query pass — essential for financial dashboards that show month-over-month, quarter-over-quarter, and year-over-year side by side.

SELECT
    MetricYear,
    MetricMonth,
    Revenue,

    -- 1 month ago
    LAG(Revenue, 1,  0) OVER (ORDER BY MetricYear, MetricMonth) AS Rev_1M_Ago,
    -- 3 months ago
    LAG(Revenue, 3,  0) OVER (ORDER BY MetricYear, MetricMonth) AS Rev_3M_Ago,
    -- 12 months ago (YoY)
    LAG(Revenue, 12, 0) OVER (ORDER BY MetricYear, MetricMonth) AS Rev_12M_Ago,

    -- YoY absolute change
    Revenue
    - LAG(Revenue, 12, 0)
          OVER (ORDER BY MetricYear, MetricMonth)               AS YoY_AbsChange,

    -- YoY % change
    ROUND(
        100.0 * (Revenue
                 - LAG(Revenue, 12, 0)
                       OVER (ORDER BY MetricYear, MetricMonth))
        / NULLIF(LAG(Revenue, 12, 0)
                     OVER (ORDER BY MetricYear, MetricMonth), 0)
    , 2)                                                        AS YoY_PctChange

FROM dbo.MonthlyRevenue
ORDER BY MetricYear, MetricMonth;

14. Performance: Indexing and Execution Plans

Offset window functions are computationally cheap once the data is sorted — they require a single pass over an ordered set. The expensive part is the sort operator that SQL Server must inject whenever the storage engine cannot provide pre-sorted rows from an index.

The Ideal Covering Index

For a query with LAG(Revenue) OVER (PARTITION BY RegionID ORDER BY SaleDate), the optimizer wants rows delivered pre-sorted by (RegionID, SaleDate) with Revenue available in the index. A covering index eliminates the sort:

-- Covering index for LAG/LEAD on sales data
CREATE INDEX IX_MonthlySales_Region_Date
    ON dbo.MonthlySales (RegionID, SaleDate)
    INCLUDE (Revenue);
-- PARTITION BY column first, ORDER BY column second, projected columns in INCLUDE

Reading the Execution Plan

Open the actual execution plan in SSMS (Ctrl+M then run). Look for these operators:

• Segment — marks partition boundaries (PARTITION BY). Low cost; expected.
• Sequence Project — implements LAG, LEAD, FIRST_VALUE, LAST_VALUE. Essentially free once data is sorted.
• Sort — added when no suitable index exists. This is the operator to eliminate with the index above. Its cost is O(N log N) and it blocks streaming.
• Window Spool — appears for LAST_VALUE with ROWS UNBOUNDED FOLLOWING. It buffers the partition to look ahead; expect higher memory grant for wide partitions.

Memory Grant Warning

-- Check for sort spills and memory grant issues
SELECT
    qs.total_worker_time / qs.execution_count AS avg_cpu_us,
    qs.total_elapsed_time / qs.execution_count AS avg_duration_us,
    qs.total_spills / qs.execution_count AS avg_spills,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1
    ) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%LAG%' OR qt.text LIKE '%LEAD%'
ORDER BY avg_spills DESC;

Summary of Best Practices

• Create a composite index with PARTITION BY columns leading, ORDER BY column next, and projected columns in INCLUDE.
• Keep partitions as large as possible — more rows per partition means fewer index seeks and sort restarts.
• For LAST_VALUE, always write the explicit frame; the Window Spool cost is predictable and acceptable for moderate data volumes.
• Fill calendar gaps before applying offset functions on time-series data to avoid off-by-one errors when months are missing.
• Use NULLIF on any denominator that passes through LAG or LEAD to guard against division-by-zero at partition boundaries.