Skip to main content

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:

idproductmonthrevenue
1Widget A2024-01-0112000
2Widget A2024-02-0114500
3Widget A2024-03-0113200
4Widget A2024-04-0115800
5Widget A2024-05-0116100
6Widget B2024-01-018000
7Widget B2024-02-017200
8Widget B2024-03-019100
9Widget B2024-04-018800
10Widget B2024-05-019500
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:

idtickertrade_dateclose_price
1ACME2024-01-15142.50
2ACME2024-01-16145.20
3ACME2024-01-17143.80
4ACME2024-01-18148.00
5ACME2024-01-19147.30
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]
)
ParameterRequiredDescription
columnYesThe column whose value you want from the previous row
offsetNoHow many rows back to look. Defaults to 1
default_valueNoValue returned when there is no previous row. Defaults to NULL
info

ORDER BY inside OVER() is required for LAG(). Without a defined order, "previous row" has no meaning, and the database would return unpredictable results.

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:

productmonthrevenueprev_month_revenue
Widget A2024-01-0112000.00NULL
Widget A2024-02-0114500.0012000.00
Widget A2024-03-0113200.0014500.00
Widget A2024-04-0115800.0013200.00
Widget A2024-05-0116100.0015800.00
Widget B2024-01-018000.00NULL
Widget B2024-02-017200.008000.00
Widget B2024-03-019100.007200.00
Widget B2024-04-018800.009100.00
Widget B2024-05-019500.008800.00

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:

productmonthrevenueprev_1_monthprev_2_months
Widget A2024-01-0112000.00NULLNULL
Widget A2024-02-0114500.0012000.00NULL
Widget A2024-03-0113200.0014500.0012000.00
Widget A2024-04-0115800.0013200.0014500.00
Widget A2024-05-0116100.0015800.0013200.00

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:

productmonthrevenueprev_month_revenue
Widget A2024-01-0112000.000.00
Widget A2024-02-0114500.0012000.00
Widget A2024-03-0113200.0014500.00
Widget A2024-04-0115800.0013200.00
Widget A2024-05-0116100.0015800.00

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:

productmonthrevenuenext_month_revenue
Widget A2024-01-0112000.0014500.00
Widget A2024-02-0114500.0013200.00
Widget A2024-03-0113200.0015800.00
Widget A2024-04-0115800.0016100.00
Widget A2024-05-0116100.00NULL

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:

tickertrade_dateclose_pricenext_day_priceprice_in_2_days
ACME2024-01-15142.50145.20143.80
ACME2024-01-16145.20143.80148.00
ACME2024-01-17143.80148.00147.30
ACME2024-01-18148.00147.30NULL
ACME2024-01-19147.30NULLNULL

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:

productmonthrevenueprev_revenuemom_changemom_change_pct
Widget A2024-01-0112000.00NULLNULLNULL
Widget A2024-02-0114500.0012000.002500.0020.83
Widget A2024-03-0113200.0014500.00-1300.00-8.97
Widget A2024-04-0115800.0013200.002600.0019.70
Widget A2024-05-0116100.0015800.00300.001.90
Widget B2024-01-018000.00NULLNULLNULL
Widget B2024-02-017200.008000.00-800.00-10.00
Widget B2024-03-019100.007200.001900.0026.39
Widget B2024-04-018800.009100.00-300.00-3.30
Widget B2024-05-019500.008800.00700.007.95

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.

tip

Notice how LAG(revenue) OVER(...) is repeated multiple times in the query. Each usage triggers the same window computation. Most modern databases are smart enough to compute it once and reuse the result. However, for readability, consider using a CTE to compute the lagged value once:

WITH revenue_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,
    revenue - prev_revenue AS mom_change,
    ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 2) AS mom_change_pct
FROM revenue_with_prev;

Same result, but cleaner and easier to maintain.

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:

tickertrade_dateclose_priceprev_closeprice_changechange_pct
ACME2024-01-15142.50NULLNULLNULL
ACME2024-01-16145.20142.502.701.89
ACME2024-01-17143.80145.20-1.40-0.96
ACME2024-01-18148.00143.804.202.92
ACME2024-01-19147.30148.00-0.70-0.47

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:

productmonthrevenueprev_revenuetrend
Widget A2024-01-0112000.00NULLN/A
Widget A2024-02-0114500.0012000.00UP
Widget A2024-03-0113200.0014500.00DOWN
Widget A2024-04-0115800.0013200.00UP
Widget A2024-05-0116100.0015800.00UP
Widget B2024-01-018000.00NULLN/A
Widget B2024-02-017200.008000.00DOWN
Widget B2024-03-019100.007200.00UP
Widget B2024-04-018800.009100.00DOWN
Widget B2024-05-019500.008800.00UP

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:

productmonthrevenuefirst_month_revenuediff_from_start
Widget A2024-01-0112000.0012000.000.00
Widget A2024-02-0114500.0012000.002500.00
Widget A2024-03-0113200.0012000.001200.00
Widget A2024-04-0115800.0012000.003800.00
Widget A2024-05-0116100.0012000.004100.00
Widget B2024-01-018000.008000.000.00
Widget B2024-02-017200.008000.00-800.00
Widget B2024-03-019100.008000.001100.00
Widget B2024-04-018800.008000.00800.00
Widget B2024-05-019500.008000.001500.00

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:

productmonthrevenuebest_month
Widget A2024-05-0116100.002024-05-01
Widget A2024-04-0115800.002024-05-01
Widget A2024-02-0114500.002024-05-01
Widget A2024-03-0113200.002024-05-01
Widget A2024-01-0112000.002024-05-01
Widget B2024-05-019500.002024-05-01
Widget B2024-03-019100.002024-05-01
Widget B2024-04-018800.002024-05-01
Widget B2024-01-018000.002024-05-01
Widget B2024-02-017200.002024-05-01

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:

productmonthrevenuelast_month_revenue
Widget A2024-01-0112000.0012000.00
Widget A2024-02-0114500.0014500.00
Widget A2024-03-0113200.0013200.00
Widget A2024-04-0115800.0015800.00
Widget A2024-05-0116100.0016100.00

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

Why LAST_VALUE() Seems Broken

When you use ORDER BY inside OVER(), the default window frame is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This means the frame ends at the current row, not at the last row of the partition. So LAST_VALUE() returns the value of the current row because it is the last row the function can see.

This is not a bug. It is the default behavior. You must explicitly extend the frame to the end of the partition.

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:

productmonthrevenuelast_month_revenue
Widget A2024-01-0112000.0016100.00
Widget A2024-02-0114500.0016100.00
Widget A2024-03-0113200.0016100.00
Widget A2024-04-0115800.0016100.00
Widget A2024-05-0116100.0016100.00

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:

productmonthrevenuefirst_revenuelast_revenue
Widget A2024-01-0112000.0012000.0016100.00
Widget A2024-02-0114500.0012000.0016100.00
Widget A2024-03-0113200.0012000.0016100.00
Widget A2024-04-0115800.0012000.0016100.00
Widget A2024-05-0116100.0012000.0016100.00
Widget B2024-01-018000.008000.009500.00
Widget B2024-02-017200.008000.009500.00
Widget B2024-03-019100.008000.009500.00
Widget B2024-04-018800.008000.009500.00
Widget B2024-05-019500.008000.009500.00
tip

FIRST_VALUE() works correctly with the default frame because the first row of the partition is always included (the frame starts at UNBOUNDED PRECEDING). LAST_VALUE() needs an explicit extended frame because the default frame ends at CURRENT ROW, cutting off all following rows.

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​

BoundaryMeaning
UNBOUNDED PRECEDINGFirst row of the partition
N PRECEDINGN rows before the current row
CURRENT ROWThe current row
N FOLLOWINGN rows after the current row
UNBOUNDED FOLLOWINGLast row of the partition

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:

KeywordBehavior
ROWSCounts physical rows regardless of their values
RANGEIncludes all rows with the same value as the boundary row
-- 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.

info

When ORDER BY is present and no explicit frame is specified, the default in the SQL standard is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This is the default behavior in PostgreSQL, SQL Server, and Oracle. Be aware that peer rows (rows with equal ORDER BY values) will be included together with RANGE, which can produce unexpected results for LAST_VALUE() even within a group of ties.

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:

productmonthrevenuelast_defaultlast_fulllast_2row
Widget A2024-01-0112000.0012000.0016100.0012000.00
Widget A2024-02-0114500.0014500.0016100.0014500.00
Widget A2024-03-0113200.0013200.0016100.0013200.00
Widget A2024-04-0115800.0015800.0016100.0015800.00
Widget A2024-05-0116100.0016100.0016100.0016100.00
  • 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:

productmonthrevenueprev_monthnext_monthfirst_monthlast_monthgrowth_from_startmom_change
Widget A2024-01-0112000.00NULL14500.0012000.0016100.000.00NULL
Widget A2024-02-0114500.0012000.0013200.0012000.0016100.002500.002500.00
Widget A2024-03-0113200.0014500.0015800.0012000.0016100.001200.00-1300.00
Widget A2024-04-0115800.0013200.0016100.0012000.0016100.003800.002600.00
Widget A2024-05-0116100.0015800.00NULL12000.0016100.004100.00300.00
Widget B2024-01-018000.00NULL7200.008000.009500.000.00NULL
Widget B2024-02-017200.008000.009100.008000.009500.00-800.00-800.00
Widget B2024-03-019100.007200.008800.008000.009500.001100.001900.00
Widget B2024-04-018800.009100.009500.008000.009500.00800.00-300.00
Widget B2024-05-019500.008800.00NULL8000.009500.001500.00700.00
tip

The WINDOW clause (supported in PostgreSQL, MySQL 8+, and SQLite 3.28+) lets you define named windows and reuse them across multiple functions. Notice how w and w_full are defined once and referenced multiple times. This avoids repetition and makes the query significantly more readable.

If your database does not support named windows, simply inline the full OVER(...) clause for each function.

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:

productmonthrevenueprev_revenue
Widget A2024-02-0114500.0012000.00
Widget A2024-04-0115800.0013200.00
Widget A2024-05-0116100.0015800.00
Widget B2024-03-019100.007200.00
Widget B2024-05-019500.008800.00

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:

FunctionWhat it returns
LAG(col, N)The value from N rows before the current row
LEAD(col, N)The value from N rows after the current row
FIRST_VALUE(col)The value from the first row of the window frame
LAST_VALUE(col)The value from the last row of the window frame

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.

Table of Contents