SQL 58 🛢️ Value Access Functions: LAG and LEAD - KandZ

The window functions so far—the ranking functions, the aggregates with OVER—compute values from the rows in the window. The LAG and LEAD are different: they access a specific row relative to the current row. The LAG looks back, the LEAD looks forward, and the offset and the default control the distance and the fallback. They are the tools for the row-to-row comparison, the running difference, the change detection, and the period-over-period analysis.

Key point: LAG(column, offset, default) returns the value of the column from the row offset rows before the current row in the window order. LEAD(column, offset, default) returns the value from the row offset rows after. The offset defaults to 1, and the default defaults to NULL. The ORDER BY inside the OVER clause defines the row order, and the PARTITION BY resets the sequence for each group. Without the ORDER BY, the result is non-deterministic.

Why LAG and LEAD exist

The row-comparison problem. The report needs the difference between the current row and the previous row. The LAG returns the previous value, and the subtraction produces the difference. The LEAD returns the next value, and the subtraction produces the forward difference. The two functions are the tools for the adjacent-row comparison.

The self-join problem. Before the window functions, the row comparison required the self-join on the sequential key. The LAG and the LEAD express the comparison without the join, and the result is the simpler query and the faster execution.

The gap problem. The rows may not be contiguous. The LAG(column, 1) returns the immediately preceding row’s value, and the LAG(column, 2) returns the value from two rows back. The offset is the distance, and the LAG is the tool for the arbitrary offset.

The boundary problem. The first row has no previous row, and the last row has no next row. The default argument is the value that is returned when the offset is beyond the boundary. The default is NULL, and the explicit default is the tool for the zero or the specific value.

The partition problem. The comparison should reset for each group. The PARTITION BY makes the LAG and the LEAD look only within the partition, and the first row of each partition has no previous row.

The ordering problem. The LAG and the LEAD depend on the row order. The ORDER BY inside the OVER clause defines the order, and the correct order is the requirement for the meaningful result.

a. The basic LAG and LEAD

The LAG(column) returns the previous row’s value.

SELECT
    order_date,
    amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_amount
FROM orders;

The prev_amount is the amount from the previous row in the date order. The first row has the NULL because there is no previous row.

The LEAD(column) returns the next row’s value.

SELECT
    order_date,
    amount,
    LEAD(amount) OVER (ORDER BY order_date) AS next_amount
FROM orders;

The next_amount is the amount from the next row. The last row has the NULL.

The difference.

SELECT
    order_date,
    amount,
    amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders;

The change is the difference between the current amount and the previous. The first row has the NULL because the LAG is the NULL.

The offset.

SELECT
    order_date,
    amount,
    LAG(amount, 2) OVER (ORDER BY order_date) AS two_back
FROM orders;

The two_back is the amount from the two rows before. The first two rows have the NULL.

The default.

SELECT
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount
FROM orders;

The prev_amount is the previous amount, and the first row has the 0 instead of the NULL. The default is the tool for the arithmetic where the NULL would propagate.

The LEAD with the default.

SELECT
    order_date,
    amount,
    LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount
FROM orders;

The next_amount is the next amount, and the last row has the 0.

b. The partition and the ordered comparison

The PARTITION BY resets the sequence for each group.

SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS prev_amount
FROM orders;

The prev_amount is the previous amount for the same customer. The first order of each customer has the NULL.

The running difference per customer.

SELECT
    customer_id,
    order_date,
    amount,
    amount - LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS change
FROM orders;

The change is the difference from the customer’s previous order. The pattern is for the per-customer analysis.

The comparison with the previous period.

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_revenue;

The prev_revenue is the previous month’s revenue, and the growth is the difference. The pattern is for the period-over-period analysis.

The percentage growth.

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        2
    ) AS growth_pct
FROM monthly_revenue;

The growth_pct is the percentage change. The NULLIF prevents the division by zero when the previous revenue is the zero.

The year-over-year comparison with the offset of 12.

SELECT
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
    revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_change
FROM monthly_revenue;

The same_month_last_year is the revenue from the 12 rows before, which is the same month in the previous year if the data is the monthly. The offset of 12 is the year.

c. The use cases

The LAG and the LEAD have the distinct use cases.

Use Case	Function
The previous value	`LAG`
The next value	`LEAD`
The change	`current - LAG`
The growth	`(current - LAG) / LAG`
The year-over-year	`LAG(, 12)`
The session duration	`LEAD(timestamp) - timestamp`
The gap detection	`timestamp - LAG(timestamp)`
The state transition	`LAG(state) != state`
The running total	`SUM OVER` (not `LAG`)
The first and last	`FIRST_VALUE`, `LAST_VALUE`

The gap detection.

SELECT
    event_time,
    event_time - LAG(event_time) OVER (ORDER BY event_time) AS gap
FROM events;

The gap is the time between the current event and the previous. The pattern is for the anomaly detection and the rate analysis.

The state transition.

SELECT
    event_time,
    state,
    LAG(state) OVER (ORDER BY event_time) AS prev_state
FROM events
WHERE state != LAG(state) OVER (ORDER BY event_time);

The prev_state is the previous state, and the filter keeps only the rows where the state changed. The pattern is for the audit trail and the state machine analysis. The WHERE with the window function is not allowed directly; the subquery is the mechanism.

SELECT * FROM (
    SELECT
        event_time,
        state,
        LAG(state) OVER (ORDER BY event_time) AS prev_state
    FROM events
) AS ranked
WHERE state != prev_state OR prev_state IS NULL;

The subquery computes the prev_state, and the outer query filters the changes. The IS NULL includes the first row.

The session duration.

SELECT
    user_id,
    login_time,
    LEAD(login_time) OVER (
        PARTITION BY user_id
        ORDER BY login_time
    ) - login_time AS session_duration
FROM logins;

The session_duration is the time between the current login and the next. The last login has the NULL because there is no next. The pattern is for the session analysis.

The first and the last in the group.

SELECT
    user_id,
    login_time,
    FIRST_VALUE(login_time) OVER (
        PARTITION BY user_id
        ORDER BY login_time
    ) AS first_login,
    LAST_VALUE(login_time) OVER (
        PARTITION BY user_id
        ORDER BY login_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_login
FROM logins;

The FIRST_VALUE and the LAST_VALUE are the related functions. The LAST_VALUE requires the full frame because the default frame stops at the current row.

The LAG with the multiple columns.

SELECT
    order_date,
    amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
    LAG(customer_id) OVER (ORDER BY order_date) AS prev_customer
FROM orders;

The two LAG calls with the different columns. The pattern is for the multi-column comparison.

The LEAD and the LAG combined.

SELECT
    value,
    LAG(value) OVER (ORDER BY id) AS prev,
    value,
    LEAD(value) OVER (ORDER BY id) AS next
FROM data;

The prev and the next in the same row. The pattern is for the three-point comparison and the local smoothing.

Complete Example Session

-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
    (1, 1, '2026-01-01', 100),
    (2, 1, '2026-01-15', 150),
    (3, 1, '2026-02-01', 200),
    (4, 2, '2026-01-05', 50),
    (5, 2, '2026-01-20', 75);

-- ============================================
-- PART 2: BASIC LAG
-- ============================================
SELECT
    order_date,
    amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_amount
FROM orders;

-- ============================================
-- PART 3: BASIC LEAD
-- ============================================
SELECT
    order_date,
    amount,
    LEAD(amount) OVER (ORDER BY order_date) AS next_amount
FROM orders;

-- ============================================
-- PART 4: THE CHANGE
-- ============================================
SELECT
    order_date,
    amount,
    amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders;

-- ============================================
-- PART 5: THE DEFAULT
-- ============================================
SELECT
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount
FROM orders;

-- ============================================
-- PART 6: WITH PARTITION
-- ============================================
SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS prev_amount
FROM orders;

-- ============================================
-- PART 7: THE GROWTH
-- ============================================
SELECT
    order_date,
    amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev,
    ROUND(
        100.0 * (amount - LAG(amount) OVER (ORDER BY order_date))
        / NULLIF(LAG(amount) OVER (ORDER BY order_date), 0),
        2
    ) AS growth_pct
FROM orders;

-- ============================================
-- PART 8: THE OFFSET
-- ============================================
SELECT
    order_date,
    amount,
    LAG(amount, 2) OVER (ORDER BY order_date) AS two_back
FROM orders;

-- ============================================
-- PART 9: THE GAP DETECTION
-- ============================================
SELECT
    order_date,
    order_date - LAG(order_date) OVER (ORDER BY order_date) AS gap
FROM orders;

-- ============================================
-- PART 10: THE STATE TRANSITION
-- ============================================
SELECT * FROM (
    SELECT
        order_date,
        customer_id,
        LAG(customer_id) OVER (ORDER BY order_date) AS prev_customer
    FROM orders
) AS ranked
WHERE customer_id != prev_customer OR prev_customer IS NULL;

The ten parts covered the sample data, the basic LAG, the basic LEAD, the change, the default, the partition, the growth, the offset, the gap detection, and the state transition.

Quick Reference

LAG and LEAD Syntax

Function	Returns
`LAG(col)`	The previous value
`LAG(col, n)`	The value n rows back
`LAG(col, n, default)`	The value with the fallback
`LEAD(col)`	The next value
`LEAD(col, n)`	The value n rows forward
`LEAD(col, n, default)`	The value with the fallback

Arguments

Argument	Default	Purpose
`column`	—	The value to return
`offset`	1	The row distance
`default`	`NULL`	The boundary value

The Comparison

Pattern	Expression
The change	`current - LAG(current)`
The growth	`(current - LAG) / LAG`
The gap	`timestamp - LAG(timestamp)`
The duration	`LEAD(timestamp) - timestamp`
The transition	`LAG(state) != state`
The year-over-year	`LAG(, 12)`

The Boundary

Row	LAG	LEAD
First	`NULL` or the default	The value
Last	The value	`NULL` or the default
First of the partition	`NULL` or the default	The value

Best Practices

✅ Do This:

-- Use LAG for the previous value
LAG(amount) OVER (ORDER BY order_date)                         -- ✅

-- Use LEAD for the next value
LEAD(amount) OVER (ORDER BY order_date)                        -- ✅

-- Use the default for the arithmetic
LAG(amount, 1, 0) OVER (ORDER BY order_date)                   -- ✅

-- Use the partition for the per-group comparison
LAG(amount) OVER (PARTITION BY customer ORDER BY date)         -- ✅

-- Use NULLIF for the division
(current - LAG(current)) / NULLIF(LAG(current), 0)             -- ✅

-- Use the subquery to filter on the window result
SELECT * FROM (
    SELECT *, LAG(x) OVER (...) AS prev FROM t
) AS ranked WHERE x != prev;                                   -- ✅

❌ Don’t Do This:

-- Don't use LAG without the ORDER BY
LAG(amount) OVER ()  -- non-deterministic                        -- ❌

-- Don't use LAG in the WHERE
WHERE amount > LAG(amount) OVER (...)  -- error                  -- ❌

-- Don't forget the NULL on the boundary
amount - LAG(amount)  -- NULL on the first row                   -- ⚠️

-- Don't divide by the LAG without the NULLIF
(current - LAG) / LAG  -- error if the LAG is 0                  -- ⚠️

-- Don't use the wrong order
LAG(amount) OVER (ORDER BY id DESC)  -- the "previous" is the next -- ⚠️

-- Don't use the LAG for the running total
LAG(amount)  -- use the SUM OVER                                  -- ⚠️

Common Pitfalls

Pitfall	Why It Happens	Fix
`NULL` on the first row	No previous row	Use the default
Window function in `WHERE`	Not allowed	Wrap in the subquery
Non-deterministic	No `ORDER BY`	Add the `ORDER BY`
Wrong direction	The `ORDER BY` reversed	Check the order
Division by zero	The `LAG` is 0	Use `NULLIF`
Partition missing	The per-group comparison	Add the `PARTITION BY`

Real-World Examples

1. The Change

SELECT order_date, amount,
       amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders;

2. The Growth

SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS growth
FROM monthly;

3. The Year-over-Year

SELECT month, revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year
FROM monthly;

4. The Gap Detection

SELECT event_time,
       event_time - LAG(event_time) OVER (ORDER BY event_time) AS gap
FROM events;

5. The Session Duration

SELECT user_id, login_time,
       LEAD(login_time) OVER (PARTITION BY user_id ORDER BY login_time)
       - login_time AS duration
FROM logins;

6. The State Transition

SELECT * FROM (
    SELECT event_time, state,
           LAG(state) OVER (ORDER BY event_time) AS prev
    FROM events
) AS ranked WHERE state != prev;

7. The Per-Customer Change

SELECT customer_id, order_date, amount,
       amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS change
FROM orders;

8. The Three-Point Comparison

SELECT value,
       LAG(value) OVER (ORDER BY id) AS prev,
       value,
       LEAD(value) OVER (ORDER BY id) AS next
FROM data;

9. The Cumulative Difference

SELECT order_date, amount,
       amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS diff
FROM orders;

10. The First and Last

SELECT user_id, login_time,
       FIRST_VALUE(login_time) OVER (PARTITION BY user_id ORDER BY login_time) AS first,
       LAST_VALUE(login_time) OVER (PARTITION BY user_id ORDER BY login_time
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last
FROM logins;

Visual

LAG and LEAD

┌─────────────────────────────────────────────────────────────┐
│  ORDER BY order_date                                        │
│                                                             │
│  ┌────────────┬────────┬──────┬──────┐                      │
│  │ order_date │ amount │ LAG  │ LEAD │                      │
│  ├────────────┼────────┼──────┼──────┤                      │
│  │ 2026-01-01 │ 100    │ NULL │ 150  │                      │
│  │ 2026-01-15 │ 150    │ 100  │ 200  │                      │
│  │ 2026-02-01 │ 200    │ 150  │ 50   │                      │
│  │ 2026-02-05 │ 50     │ 200  │ 75   │                      │
│  │ 2026-02-20 │ 75     │ 50   │ NULL │                      │
│  └────────────┴────────┴──────┴──────┘                      │
│                                                             │
│  The LAG looks back, the LEAD looks forward.                │
│  The boundary rows have the NULL.                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

The Change

┌─────────────────────────────────────────────────────────────┐
│  amount - LAG(amount) OVER (ORDER BY order_date)            │
│                                                             │
│  ┌────────────┬────────┬────────┐                           │
│  │ order_date │ amount │ change │                           │
│  ├────────────┼────────┼────────┤                           │
│  │ 2026-01-01 │ 100    │ NULL   │                           │
│  │ 2026-01-15 │ 150    │ +50    │                           │
│  │ 2026-02-01 │ 200    │ +50    │                           │
│  │ 2026-02-05 │ 50     │ -150   │                           │
│  │ 2026-02-20 │ 75     │ +25    │                           │
│  └────────────┴────────┴────────┘                           │
│                                                             │
│  The first row has the NULL because there is no previous.   │
│  The pattern is for the row-to-row comparison.              │
│                                                             │
└─────────────────────────────────────────────────────────────┘

The Partition

┌─────────────────────────────────────────────────────────────┐
│  PARTITION BY customer_id ORDER BY order_date               │
│                                                             │
│  Customer 1:                                                │
│  ┌────────────┬────────┬──────┐                             │
│  │ order_date │ amount │ LAG  │                             │
│  ├────────────┼────────┼──────┤                             │
│  │ 2026-01-01 │ 100    │ NULL │                             │
│  │ 2026-01-15 │ 150    │ 100  │                             │
│  │ 2026-02-01 │ 200    │ 150  │                             │
│  └────────────┴────────┴──────┘                             │
│                                                             │
│  Customer 2:                                                │
│  ┌────────────┬────────┬──────┐                             │
│  │ order_date │ amount │ LAG  │                             │
│  ├────────────┼────────┼──────┤                             │
│  │ 2026-01-05 │ 50     │ NULL │  ← the partition resets     │
│  │ 2026-01-20 │ 75     │ 50   │                             │
│  └────────────┴────────┴──────┘                             │
│                                                             │
│  The first row of each partition has the NULL.              │
│                                                             │
└─────────────────────────────────────────────────────────────┘

The Default

┌─────────────────────────────────────────────────────────────┐
│  LAG(amount, 1, 0) OVER (ORDER BY order_date)               │
│                                                             │
│  ┌────────────┬────────┬──────┐                             │
│  │ order_date │ amount │ prev │                             │
│  ├────────────┼────────┼──────┤                             │
│  │ 2026-01-01 │ 100    │ 0    │  ← the default, not NULL    │
│  │ 2026-01-15 │ 150    │ 100  │                             │
│  │ 2026-02-01 │ 200    │ 150  │                             │
│  └────────────┴────────┴──────┘                             │
│                                                             │
│  The default is the tool for the arithmetic where the       │
│  NULL would propagate.                                      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Summary

Item	Value
`LAG(col)`	The previous value
`LEAD(col)`	The next value
`LAG(col, n)`	n rows back
`LEAD(col, n)`	n rows forward
`LAG(col, n, d)`	With the default
`LEAD(col, n, d)`	With the default
`ORDER BY`	Required
`PARTITION BY`	Optional, resets
The boundary	`NULL` or the default
The change	`current - LAG`
The growth	`(current - LAG) / LAG`
The filter	Subquery + outer `WHERE`

Key takeaways:

The LAG and the LEAD access the adjacent rows. The LAG looks back the offset rows, and the LEAD looks forward. The offset defaults to 1, and the default defaults to the NULL.
The ORDER BY determines the row order. The LAG and the LEAD depend on the order, and the meaningful result requires the correct ORDER BY. The ORDER BY inside the OVER clause is the requirement.
The PARTITION BY resets the sequence. The first row of each partition has no previous row, and the LAG returns the NULL or the default. The pattern is for the per-group comparison.
The boundary rows have the NULL or the default. The first row has no previous, and the last row has no next. The default argument is the fallback, and the explicit default is the tool for the arithmetic.
The LAG and the LEAD cannot be used in the WHERE. The window function is evaluated after the WHERE. The filter on the comparison requires the subquery: the inner query computes the LAG, and the outer query filters.
The change and the growth are the standard patterns. The current - LAG(current) is the difference, and the (current - LAG) / LAG is the growth. The NULLIF prevents the division by zero.
The gap detection and the session duration are the common use cases. The event_time - LAG(event_time) is the gap, and the LEAD(login_time) - login_time is the session duration. The patterns are for the time-series analysis.

Remember: The LAG and the LEAD are the tools for the row-to-row comparison. The LAG looks back, the LEAD looks forward, and the offset and the default control the distance and the fallback. The ORDER BY determines the order, and the PARTITION BY resets the sequence. The change, the growth, the gap, and the duration are the patterns. The LAG and the LEAD cannot be used in the WHERE, and the subquery is the mechanism. The two functions are the replacements for the self-join, and the result is the simpler query and the faster execution.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!