LEAD & LAG

Intermediate

Window Functions

LAG accesses the value of a column from a previous row; LEAD accesses a future row — both within the current partition — enabling period-over-period comparisons without self-joins.

Overview

LAG(expr, offset, default) and LEAD(expr, offset, default) look backward and forward respectively within the window defined by OVER(). The offset defaults to 1 (immediately adjacent row). When there is no prior or next row, NULL is returned unless a default value is provided as the third argument. These functions are ideal for month-over-month analysis, detecting consecutive-day activity, status transitions, and gap detection in sequences — all without the performance cost of a self-join.

Month-over-Month Revenue Change

Use LAG with ORDER BY on a date partition to pull the previous period's revenue alongside the current one, then calculate the delta in the outer SELECT.

SQL — LAG for month-over-month revenue

-- Monthly revenue and MoM change
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount)                      AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)                        AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month)              AS abs_change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
              / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        2
    )                                                          AS pct_change
FROM monthly_revenue
ORDER BY month;

Detecting Consecutive Days and Status Transitions

LAG lets you compare a row's value to the immediately preceding row in the partition, making it easy to detect status changes or consecutive-day patterns without a self-join.

SQL — LAG for consecutive days and status transitions

-- Find users who placed orders on two consecutive days
WITH user_orders AS (
    SELECT DISTINCT
        user_id,
        created_at::date AS order_date
    FROM orders
),
with_prev AS (
    SELECT
        user_id,
        order_date,
        LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order_date
    FROM user_orders
)
SELECT user_id, order_date, prev_order_date
FROM with_prev
WHERE order_date - prev_order_date = 1;   -- exactly 1 day apart

-- Detect when an order status changed from 'pending' to 'completed'
WITH status_log AS (
    SELECT
        id,
        user_id,
        status,
        created_at,
        LAG(status) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_status
    FROM orders
)
SELECT id, user_id, prev_status, status, created_at
FROM status_log
WHERE prev_status = 'pending' AND status = 'completed';

LEAD for Forward Comparisons and Gap Detection

LEAD looks ahead in the same partition. It is useful for finding gaps in sequences, computing time-to-next-event, or identifying the last event before a state change.

SQL — LEAD for forward look and gap detection

-- Time between consecutive orders per user (LEAD for next order date)
SELECT
    id,
    user_id,
    created_at,
    LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order_at,
    LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at) - created_at
        AS gap_to_next_order
FROM orders;

-- Default value when no next row exists (last order per user → gap is NULL)
SELECT
    id,
    user_id,
    amount,
    LEAD(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order_amount
FROM orders;
-- When there is no next order, returns 0 instead of NULL

Key Points to Remember

1LAG(col, n, default) returns the value from n rows before the current row in the window; LEAD looks n rows ahead.
2Without a default, both functions return NULL when the offset falls outside the partition.
3Period-over-period analysis (MoM, YoY) with LAG is more efficient than a self-join on the same table.
4Wrap NULLIF around the LAG value in percentage calculations to avoid division-by-zero errors.
5Combine DISTINCT + LAG to detect consecutive-day patterns without duplicates per day.
6Status transition detection: compare current status to LAG(status) within user partitions.

Interview Questions

How would you calculate month-over-month revenue growth using a window function?

MediumAmazon

Find users who placed orders on at least two consecutive days.

HardSwiggy

What is the third argument of LAG/LEAD and when would you use it?

EasyAtlassian

How does LAG differ from a self-join for accessing a previous row's value?

MediumGoogle

Ask Aria about LEAD & LAG

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

ROW_NUMBER, RANK & DENSE_RANK

FIRST_VALUE & LAST_VALUE