Period Comparison Patterns — YoY, MoM, and Rolling Windows

Period comparison is one of the most common analytical patterns in business SQL: how does this month compare to last month? This quarter to the same quarter last year? How is a 30-day rolling metric trending? These patterns appear in every dashboard, every board report, every product review.

This lesson collects the definitive patterns — built with LAG, LEAD, and frame clauses — and shows how to handle the edge cases that break naive implementations.

Month-over-month with LAG

The baseline pattern — already introduced in the LAG lesson, now with proper edge-case handling:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', o.created_at)    AS month,
        COUNT(DISTINCT o.order_id)            AS orders,
        COUNT(DISTINCT o.customer_id)         AS customers,
        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,
    orders,
    customers,
    LAG(revenue)   OVER (ORDER BY month)   AS prev_revenue,
    LAG(orders)    OVER (ORDER BY month)   AS prev_orders,
    LAG(customers) OVER (ORDER BY month)   AS prev_customers,
    ROUND((revenue   - LAG(revenue)   OVER (ORDER BY month)) * 100.0
        / NULLIF(LAG(revenue)   OVER (ORDER BY month), 0), 1) AS rev_mom_pct,
    ROUND((orders    - LAG(orders)    OVER (ORDER BY month)) * 100.0
        / NULLIF(LAG(orders)    OVER (ORDER BY month), 0), 1) AS ord_mom_pct
FROM monthly
ORDER BY month;

NULLIF(..., 0) prevents division by zero. The first month has NULL for all comparison columns — handled gracefully by NULL arithmetic (NULL result for the first row's growth columns).

Year-over-year with LAG(x, 12)

For YoY comparison on monthly data, LAG with an offset of 12 goes back exactly 12 months:

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, 12) OVER (ORDER BY month)           AS same_month_last_year,
    ROUND(
        (revenue - LAG(revenue, 12) OVER (ORDER BY month)) * 100.0
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0),
        1
    )                                                AS yoy_pct
FROM monthly
ORDER BY month;

This requires at least 12 months of data. The first 12 months will have NULL for same_month_last_year — filter those out or show them explicitly.

Caveat: LAG(x, 12) works cleanly when data has exactly one row per month. If any months are missing (no orders), the 12-row offset lands on the wrong month. Robust YoY requires a self-join on the month value rather than a position offset:

-- Robust YoY with self-join (handles missing months)
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
    curr.month,
    curr.revenue,
    prev.revenue                                         AS same_month_last_year,
    ROUND(
        (curr.revenue - prev.revenue) * 100.0
        / NULLIF(prev.revenue, 0),
        1
    )                                                   AS yoy_pct
FROM monthly curr
LEFT JOIN monthly prev
       ON prev.month = curr.month - INTERVAL '1 year'
ORDER BY curr.month;

Purchase this course to unlock the full lesson.

← Previous Next →