SQL LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() as Window Functions

Some of the most valuable questions in data analysis involve comparing a row to its neighbors: How do this month's sales compare to last month's? What was the first order a customer ever placed? What is the difference between today's stock price and tomorrow's closing price? Answering these questions in SQL used to require painful self-joins and correlated subqueries. With the SQL LAG LEAD family of window functions, these comparisons become elegant one-liners.

This guide covers four navigation window functions: LAG(), LEAD(), FIRST_VALUE(), and LAST_VALUE(). You will learn how each one works, how to use them for period-over-period comparisons, and how the ROWS BETWEEN frame specification controls exactly which rows these functions can see. Every concept includes practical examples with clear outputs.

Prerequisites

You should be comfortable with SQL window function basics, including the OVER() clause, PARTITION BY, and ORDER BY inside OVER(). All four functions in this guide are dedicated window functions. They can only be used with the OVER() clause and have no standalone aggregate equivalent.

The Sample Data

We will use two tables throughout this guide.

monthly_revenue table:

CREATE TABLE monthly_revenue (
    id INT PRIMARY KEY,
    product VARCHAR(50) NOT NULL,
    month DATE NOT NULL,
    revenue DECIMAL(12,2) NOT NULL
);
INSERT INTO monthly_revenue (id, product, month, revenue) VALUES
(1,  'Widget A', '2024-01-01', 12000),
(2,  'Widget A', '2024-02-01', 14500),
(3,  'Widget A', '2024-03-01', 13200),
(4,  'Widget A', '2024-04-01', 15800),
(5,  'Widget A', '2024-05-01', 16100),
(6,  'Widget B', '2024-01-01', 8000),
(7,  'Widget B', '2024-02-01', 7200),
(8,  'Widget B', '2024-03-01', 9100),
(9,  'Widget B', '2024-04-01', 8800),
(10, 'Widget B', '2024-05-01', 9500);

daily_prices table:

CREATE TABLE daily_prices (
    id INT PRIMARY KEY,
    ticker VARCHAR(10) NOT NULL,
    trade_date DATE NOT NULL,
    close_price DECIMAL(10,2) NOT NULL
);
INSERT INTO daily_prices (id, ticker, trade_date, close_price) VALUES
(1, 'ACME', '2024-01-15', 142.50),
(2, 'ACME', '2024-01-16', 145.20),
(3, 'ACME', '2024-01-17', 143.80),
(4, 'ACME', '2024-01-18', 148.00),
(5, 'ACME', '2024-01-19', 147.30);

LAG(): Accessing Previous Row Values

LAG() retrieves a value from a previous row relative to the current row, based on the order defined in OVER(). Think of it as "looking backward" in your sorted result set.

Syntax

LAG(column, offset, default_value) OVER (
    [PARTITION BY column1, ...]
    ORDER BY column2 [ASC|DESC]
)

Basic Example: Previous Month's Revenue

SELECT
    product,
    month,
    revenue,
    LAG(revenue) OVER (
        PARTITION BY product
        ORDER BY month
    ) AS prev_month_revenue
FROM monthly_revenue;

Output:

The first row of each product partition returns NULL because there is no previous row to look at. Every other row successfully retrieves the revenue from one row back.

Specifying an Offset

By default, LAG() looks back 1 row. You can change this by providing a second argument:

SELECT
    product,
    month,
    revenue,
    LAG(revenue, 1) OVER (PARTITION BY product ORDER BY month) AS prev_1_month,
    LAG(revenue, 2) OVER (PARTITION BY product ORDER BY month) AS prev_2_months
FROM monthly_revenue
WHERE product = 'Widget A';

Output:

LAG(revenue, 2) looks back 2 rows. January and February return NULL because there are fewer than 2 preceding rows available.

Providing a Default Value

To avoid NULL on the first rows, pass a third argument as the default:

SELECT
    product,
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (
        PARTITION BY product
        ORDER BY month
    ) AS prev_month_revenue
FROM monthly_revenue
WHERE product = 'Widget A';

Output:

January now shows 0 instead of NULL. Choose a default that makes sense for your use case. In some scenarios, keeping NULL is actually preferable since it clearly indicates "no data available."

LEAD(): Accessing Next Row Values

LEAD() is the mirror image of LAG(). It retrieves a value from a subsequent row relative to the current row. Think of it as "looking forward."

Syntax

LEAD(column, offset, default_value) OVER (
    [PARTITION BY column1, ...]
    ORDER BY column2 [ASC|DESC]
)

The parameters are identical to LAG(), but instead of looking backward, LEAD() looks forward.

Basic Example: Next Month's Revenue

SELECT
    product,
    month,
    revenue,
    LEAD(revenue) OVER (
        PARTITION BY product
        ORDER BY month
    ) AS next_month_revenue
FROM monthly_revenue
WHERE product = 'Widget A';

Output:

This time, the last row returns NULL because there is no future row to look at. Every other row successfully retrieves the revenue from the next month.

LEAD() with Offset

SELECT
    ticker,
    trade_date,
    close_price,
    LEAD(close_price, 1) OVER (ORDER BY trade_date) AS next_day_price,
    LEAD(close_price, 2) OVER (ORDER BY trade_date) AS price_in_2_days
FROM daily_prices;

Output:

Period-Over-Period Comparisons

The most common real-world use of SQL LAG LEAD functions is period-over-period analysis: comparing a current value against a previous period to calculate differences, growth rates, or trends.

Month-Over-Month Change (Absolute and Percentage)

SELECT
    product,
    month,
    revenue,
    LAG(revenue) OVER (
        PARTITION BY product ORDER BY month
    ) AS prev_revenue,
    revenue - LAG(revenue) OVER (
        PARTITION BY product ORDER BY month
    ) AS mom_change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (PARTITION BY product ORDER BY month))
              / LAG(revenue) OVER (PARTITION BY product ORDER BY month),
        2
    ) AS mom_change_pct
FROM monthly_revenue;

Output:

This single query gives you a complete month-over-month analysis. Widget A grew 20.83% from January to February but dipped 8.97% in March. Widget B dropped 10% in February before rebounding 26.39% in March.

Day-Over-Day Price Change

SELECT
    ticker,
    trade_date,
    close_price,
    LAG(close_price) OVER (ORDER BY trade_date) AS prev_close,
    ROUND(close_price - LAG(close_price) OVER (ORDER BY trade_date), 2) AS price_change,
    ROUND(
        100.0 * (close_price - LAG(close_price) OVER (ORDER BY trade_date))
              / LAG(close_price) OVER (ORDER BY trade_date),
        2
    ) AS change_pct
FROM daily_prices;

Output:

Detecting Trend Direction with LAG

You can combine LAG() with CASE to label each period's trend:

SELECT
    product,
    month,
    revenue,
    LAG(revenue) OVER (PARTITION BY product ORDER BY month) AS prev_revenue,
    CASE
        WHEN LAG(revenue) OVER (PARTITION BY product ORDER BY month) IS NULL THEN 'N/A'
        WHEN revenue > LAG(revenue) OVER (PARTITION BY product ORDER BY month) THEN 'UP'
        WHEN revenue < LAG(revenue) OVER (PARTITION BY product ORDER BY month) THEN 'DOWN'
        ELSE 'FLAT'
    END AS trend
FROM monthly_revenue;

Output:

FIRST_VALUE(): The First Row in the Window

FIRST_VALUE() returns the value from the first row of the window frame. It is commonly used to compare every row against a baseline, such as the first month's revenue, the initial stock price, or the earliest record in a partition.

Syntax

FIRST_VALUE(column) OVER (
    [PARTITION BY column1, ...]
    ORDER BY column2 [ASC|DESC]
    [frame_clause]
)

Example: Compare Every Month to the First Month

SELECT
    product,
    month,
    revenue,
    FIRST_VALUE(revenue) OVER (
        PARTITION BY product
        ORDER BY month
    ) AS first_month_revenue,
    revenue - FIRST_VALUE(revenue) OVER (
        PARTITION BY product
        ORDER BY month
    ) AS diff_from_start
FROM monthly_revenue;

Output:

Widget A has grown 4100 since its first month. Widget B dipped in February but ended up 1500 above its starting point by May. This "comparison to baseline" pattern is extremely common in financial reporting and KPI dashboards.

Example: Highest Earner's Name in Each Group

FIRST_VALUE() works on any data type, not just numbers. You can retrieve a text value from the first row:

SELECT
    product,
    month,
    revenue,
    FIRST_VALUE(month) OVER (
        PARTITION BY product
        ORDER BY revenue DESC
    ) AS best_month
FROM monthly_revenue;

Output:

By ordering by revenue DESC, the first row in each partition is the highest-revenue month. FIRST_VALUE(month) then pulls that month's date onto every row.

LAST_VALUE(): The Last Row in the Window

LAST_VALUE() returns the value from the last row of the window frame. In theory, this is the counterpart to FIRST_VALUE(). In practice, it has a notorious pitfall that catches almost everyone the first time.

Syntax

LAST_VALUE(column) OVER (
    [PARTITION BY column1, ...]
    ORDER BY column2 [ASC|DESC]
    [frame_clause]
)

The LAST_VALUE() Trap

Let's try to get the last month's revenue (May) on every row:

-- COMMON MISTAKE: this does NOT work as expected
SELECT
    product,
    month,
    revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY product
        ORDER BY month
    ) AS last_month_revenue
FROM monthly_revenue
WHERE product = 'Widget A';

Output:

That looks wrong. last_month_revenue is just mirroring the current row's revenue. Why?

The Fix: Extend the Frame

To make LAST_VALUE() see the entire partition, specify the frame explicitly:

SELECT
    product,
    month,
    revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY product
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_month_revenue
FROM monthly_revenue
WHERE product = 'Widget A';

Output:

Now every row correctly shows 16100 (May's revenue) as the last month's value. The key addition is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, which tells the database to consider all rows in the partition for this function.

FIRST_VALUE vs LAST_VALUE Side by Side

SELECT
    product,
    month,
    revenue,
    FIRST_VALUE(revenue) OVER (
        PARTITION BY product
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY product
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_revenue
FROM monthly_revenue;

Output:

Frame Specification: ROWS BETWEEN

The ROWS BETWEEN clause gives you precise control over which rows a window function can access. This is the mechanism behind moving averages, sliding windows, and the LAST_VALUE() fix we just saw. Understanding it deeply will unlock the full potential of SQL LAG LEAD and all other window functions.

Syntax

function() OVER (
    [PARTITION BY ...]
    ORDER BY ...
    ROWS BETWEEN <start_bound> AND <end_bound>
)

Available Boundaries

Visual Representation

Imagine a partition with 7 rows and the current row is row 4:

Row 1  ← UNBOUNDED PRECEDING
Row 2  ← 2 PRECEDING
Row 3  ← 1 PRECEDING
Row 4  ← CURRENT ROW
Row 5  ← 1 FOLLOWING
Row 6  ← 2 FOLLOWING
Row 7  ← UNBOUNDED FOLLOWING

Common Frame Definitions

-- Running total (default when ORDER BY is present)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Entire partition (needed for LAST_VALUE to work correctly)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- 3-row moving window (current + 2 previous)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

-- Centered window (1 before, current, 1 after)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- Forward-looking window
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING

ROWS vs RANGE

SQL also supports RANGE BETWEEN, which behaves differently from ROWS BETWEEN when there are tied values in the ORDER BY column:

-- ROWS: strictly row-by-row, one at a time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- RANGE: includes all peers (ties) up to the current value
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

For most use cases with LAG, LEAD, FIRST_VALUE, and LAST_VALUE, you will want ROWS since it gives more predictable, granular control.

Practical Example: Frame Impact on FIRST_VALUE and LAST_VALUE

SELECT
    product,
    month,
    revenue,

    -- Default frame (UNBOUNDED PRECEDING to CURRENT ROW)
    LAST_VALUE(revenue) OVER (
        PARTITION BY product ORDER BY month
    ) AS last_default,

    -- Full partition frame
    LAST_VALUE(revenue) OVER (
        PARTITION BY product ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_full,

    -- 2-row trailing window
    LAST_VALUE(revenue) OVER (
        PARTITION BY product ORDER BY month
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS last_2row
FROM monthly_revenue
WHERE product = 'Widget A';

Output:

• last_default: Equals the current row (frame ends at current row)
• last_full: Always 16100 (frame covers entire partition)
• last_2row: Equals the current row (it is the last of the 2-row window)

This clearly shows how the frame definition changes what LAST_VALUE() returns.

Combining LAG, LEAD, FIRST_VALUE, and LAST_VALUE

Here is a comprehensive query that uses all four functions together for a complete analytical view:

SELECT
    product,
    month,
    revenue,
    LAG(revenue) OVER w AS prev_month,
    LEAD(revenue) OVER w AS next_month,
    FIRST_VALUE(revenue) OVER w_full AS first_month,
    LAST_VALUE(revenue) OVER w_full AS last_month,
    revenue - FIRST_VALUE(revenue) OVER w_full AS growth_from_start,
    revenue - LAG(revenue) OVER w AS mom_change
FROM monthly_revenue
WINDOW
    w      AS (PARTITION BY product ORDER BY month),
    w_full AS (PARTITION BY product ORDER BY month
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Output:

Common Mistakes to Avoid

Mistake 1: Forgetting ORDER BY with LAG/LEAD

-- WRONG: No ORDER BY means "previous" is meaningless
SELECT
    product,
    month,
    revenue,
    LAG(revenue) OVER (PARTITION BY product) AS prev_revenue
FROM monthly_revenue;

Without ORDER BY, the database has no way to determine which row is "previous." Some databases will throw an error; others will return unpredictable results.

Fix: Always include ORDER BY:

SELECT
    product,
    month,
    revenue,
    LAG(revenue) OVER (PARTITION BY product ORDER BY month) AS prev_revenue
FROM monthly_revenue;

Mistake 2: Using LAG/LEAD in WHERE

Like all window functions, LAG() and LEAD() cannot be used in WHERE or HAVING:

-- WRONG: Will fail
SELECT product, month, revenue
FROM monthly_revenue
WHERE revenue > LAG(revenue) OVER (PARTITION BY product ORDER BY month);

Fix: Wrap in a CTE or subquery:

WITH with_prev AS (
    SELECT
        product,
        month,
        revenue,
        LAG(revenue) OVER (PARTITION BY product ORDER BY month) AS prev_revenue
    FROM monthly_revenue
)
SELECT product, month, revenue, prev_revenue
FROM with_prev
WHERE revenue > prev_revenue;

Output:

This returns only the months where revenue increased compared to the previous month.

Mistake 3: Not Extending the Frame for LAST_VALUE

This was covered in detail above, but it is worth repeating because it is the single most common error with LAST_VALUE():

-- WRONG: Returns the current row's value, not the partition's last value
LAST_VALUE(revenue) OVER (PARTITION BY product ORDER BY month)

-- CORRECT: Extends the frame to the end of the partition
LAST_VALUE(revenue) OVER (
    PARTITION BY product ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Mistake 4: Confusing LAG/LEAD with FIRST_VALUE/LAST_VALUE

These function pairs solve different problems:

LAG and LEAD are relative to the current row (always a fixed offset). FIRST_VALUE and LAST_VALUE are relative to the window frame boundaries (which can be the entire partition or a sliding window). Do not use LAG(col, 3) when you actually need the first value of a partition. They may happen to produce the same result on row 4, but they will diverge on every other row.

Summary

The SQL LAG LEAD family of window functions gives you the ability to compare rows with their neighbors without self-joins or correlated subqueries:

• LAG(column, offset, default) looks backward to retrieve a value from a previous row. Perfect for month-over-month and day-over-day comparisons.
• LEAD(column, offset, default) looks forward to retrieve a value from a subsequent row. Useful for forecasting comparisons and detecting future changes.
• FIRST_VALUE(column) returns the value from the first row of the window frame. Ideal for comparing every row against a baseline or starting point.
• LAST_VALUE(column) returns the value from the last row of the window frame. Requires an explicit frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to work as most people expect.
• The ROWS BETWEEN frame specification controls exactly which rows a window function can see, enabling sliding windows, forward-looking computations, and correct LAST_VALUE() behavior.

These four functions are the backbone of period-over-period analysis, trend detection, and baseline comparisons in SQL. Combined with PARTITION BY, they let you perform these calculations independently within each group, making them indispensable for reporting and analytics.