LAG() and LEAD() — Accessing Adjacent Rows

LAG() and LEAD() let you access values from other rows in the window — specifically rows before or after the current row. They're the standard tools for period-over-period comparisons: month-over-month growth, day-over-day change, detecting consecutive patterns.

Basic syntax

LAG(expression, offset, default)  OVER (PARTITION BY ... ORDER BY ...)
LEAD(expression, offset, default) OVER (PARTITION BY ... ORDER BY ...)

expression: the column or expression to look at in the offset row
offset: how many rows back (LAG) or forward (LEAD) — defaults to 1
default: value to return when the offset row doesn't exist (first/last row) — defaults to NULL

Month-over-month revenue comparison

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', o.created_at)    AS month,
        SUM(oi.quantity * oi.unit_price)     AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY DATE_TRUNC('month', o.created_at)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)                           AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month)                 AS mom_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        * 100.0
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        1
    )                                                            AS mom_pct_change
FROM monthly
ORDER BY month;

The first month has no previous month, so LAG(revenue) returns NULL there — and the change columns also become NULL. NULLIF(..., 0) prevents division by zero if a previous month had zero revenue.

Default values for boundary rows

Instead of NULL at boundaries, provide a meaningful default:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', o.created_at)    AS month,
        SUM(oi.quantity * oi.unit_price)     AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY DATE_TRUNC('month', o.created_at)
)
SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month)    AS prev_month_revenue
FROM monthly
ORDER BY month;

The third argument 0 means: if there is no previous row, return 0 instead of NULL. This makes downstream arithmetic cleaner — no NULLs to COALESCE around.

Purchase this course to unlock the full lesson.

← Previous Next →