SQL Window Frames, LAG, and LEAD — Moving Averages and Period Comparisons

What window frames control

When you add ORDER BY to a window function, the database needs to know which rows within the partition to include in each calculation. That set of rows is called the frame. Without an explicit frame clause, the default is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This means "all rows from the start of the partition up to (and including) all rows with the same ORDER BY value as the current row". For most use cases you want ROWS BETWEEN instead, which counts physical rows not value ranges.

ROWS vs RANGE

-- Running total using ROWS (physical position — recommended for running totals)
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_revenue_summary;

-- RANGE would include all rows with the same order_date in the "current" group
-- If two dates are equal, they get the same running total (often unexpected)

Use ROWS BETWEEN for running totals and moving averages where you want an exact count of preceding rows.

Moving averages

A 7-day moving average smooths out daily noise in metrics like revenue or signups. The frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "this row plus the 6 rows before it" — 7 rows total.

-- 7-day moving average of daily revenue
SELECT
    order_date,
    daily_revenue,
    ROUND(
        AVG(daily_revenue) OVER (
            ORDER BY order_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2
    ) AS revenue_7d_avg
FROM (
    SELECT
        DATE(created_at) AS order_date,
        SUM(total_amount) AS daily_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE(created_at)
) daily
ORDER BY order_date;

For the first 6 rows (where fewer than 7 days exist), the average is computed over however many rows are available — there is no implicit padding.

LAG and LEAD — accessing adjacent rows

LAG(col, n) returns the value of col from n rows before the current row. LEAD(col, n) returns it from n rows ahead. Both return NULL if the offset row does not exist (supply a third argument as the default).

-- Month-over-month revenue change
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount)               AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', 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(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1
    ) AS mom_pct_change
FROM monthly
ORDER BY month;

-- Session gap detection: find sessions with > 30 min gap from previous
SELECT
    session_id,
    user_id,
    started_at,
    LAG(started_at) OVER (PARTITION BY user_id ORDER BY started_at) AS prev_start,
    started_at - LAG(started_at) OVER (PARTITION BY user_id ORDER BY started_at)
        AS gap
FROM user_sessions
ORDER BY user_id, started_at;

FIRST_VALUE and LAST_VALUE

FIRST_VALUE(col) returns the value from the first row of the window frame. LAST_VALUE(col) returns the value from the last row — but the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) means LAST_VALUE sees only rows up to the current one. To get the true last value of the partition, extend the frame explicitly.

-- Each order compared to the customer's first and most recent order amount
SELECT
    id,
    customer_id,
    total_amount,
    created_at,
    FIRST_VALUE(total_amount) OVER (
        PARTITION BY customer_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_order_amount,
    LAST_VALUE(total_amount) OVER (
        PARTITION BY customer_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_order_amount
FROM orders;

Year-over-year comparison with LAG

-- YoY revenue comparison per product category
WITH yearly AS (
    SELECT
        EXTRACT(YEAR FROM o.created_at) AS yr,
        p.category,
        SUM(i.unit_price * i.quantity)  AS revenue
    FROM order_items i
    JOIN products p ON p.id = i.product_id
    JOIN orders   o ON o.id = i.order_id
    GROUP BY yr, p.category
)
SELECT
    yr,
    category,
    revenue,
    LAG(revenue) OVER (PARTITION BY category ORDER BY yr) AS prev_year,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY yr))
          / NULLIF(LAG(revenue) OVER (PARTITION BY category ORDER BY yr), 0), 1)
        AS yoy_pct
FROM yearly
ORDER BY category, yr;

Recap

Window frames control which rows each window calculation sees. Use ROWS BETWEEN for running totals and moving averages where physical row position matters. LAG and LEAD access values from adjacent rows — the cleanest pattern for period-over-period comparisons. FIRST_VALUE / LAST_VALUE need an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame to see the whole partition rather than just up to the current row.