Period Comparison Patterns — YoY, MoM, WoW

Period comparisons — Year-over-Year (YoY), Month-over-Month (MoM), Week-over-Week (WoW) — are among the most requested analyses in any business. CTEs make these clean, readable, and correct.

The Core Pattern

All period comparisons follow the same structure:

Aggregate the metric for both the current period and the comparison period
Join on the entity (product, customer, etc.)
Compute the delta and percentage change

Example 1: Monthly Revenue MoM

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.created_at)::DATE AS month,
        SUM(oi.quantity * oi.unit_price)         AS revenue,
        COUNT(DISTINCT o.order_id)               AS ec_orders,
        COUNT(DISTINCT o.customer_id)            AS ec_customers
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    GROUP BY 1
),

with_prev AS (
    SELECT
        month,
        revenue,
        ec_orders,
        ec_customers,
        LAG(revenue)   OVER (ORDER BY month) AS prev_revenue,
        LAG(ec_orders)    OVER (ORDER BY month) AS prev_orders,
        LAG(ec_customers) OVER (ORDER BY month) AS prev_customers
    FROM monthly_revenue
)

SELECT
    month,
    ROUND(revenue, 2)               AS revenue,
    ROUND(prev_revenue, 2)          AS prev_month_revenue,
    ROUND(revenue - prev_revenue, 2) AS revenue_delta,
    ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS revenue_mom_pct,
    ec_orders,
    ROUND(100.0 * (ec_orders - prev_orders) / NULLIF(prev_orders, 0), 1) AS orders_mom_pct,
    ec_customers,
    ROUND(100.0 * (ec_customers - prev_customers) / NULLIF(prev_customers, 0), 1) AS customers_mom_pct
FROM with_prev
WHERE prev_revenue IS NOT NULL
ORDER BY month;

Example 2: Year-over-Year by Category

Compare this year's category revenue to last year's same period:

WITH category_annual AS (
    SELECT
        p.category,
        EXTRACT(YEAR FROM o.created_at)::INT AS year,
        SUM(oi.quantity * oi.unit_price)     AS revenue
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    JOIN ec_products p     ON p.product_id = oi.product_id
    GROUP BY p.category, year
),

yoy AS (
    SELECT
        category,
        year,
        revenue,
        LAG(revenue) OVER (PARTITION BY category ORDER BY year) AS prev_year_revenue
    FROM category_annual
)

SELECT
    category,
    year,
    ROUND(revenue, 2)               AS revenue,
    ROUND(prev_year_revenue, 2)     AS prev_year,
    ROUND(revenue - prev_year_revenue, 2) AS yoy_delta,
    ROUND(100.0 * (revenue - prev_year_revenue) / NULLIF(prev_year_revenue, 0), 1) AS yoy_pct
FROM yoy
WHERE prev_year_revenue IS NOT NULL
ORDER BY category, year;

Example 3: Same Period Last Year (SPLY) — Exact Date Match

For a specific month, compare to the same month last year (not just the previous month):

WITH this_period AS (
    SELECT
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    JOIN ec_products p     ON p.product_id = oi.product_id
    WHERE DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY p.category
),

last_year_same_period AS (
    SELECT
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue_ly
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    JOIN ec_products p     ON p.product_id = oi.product_id
    WHERE DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 year'
    GROUP BY p.category
)

SELECT
    tp.category,
    ROUND(tp.revenue, 2)                      AS this_period,
    ROUND(ly.revenue_ly, 2)                   AS same_period_last_year,
    ROUND(tp.revenue - ly.revenue_ly, 2)      AS delta,
    ROUND(100.0 * (tp.revenue - ly.revenue_ly) / NULLIF(ly.revenue_ly, 0), 1) AS yoy_pct
FROM this_period tp
LEFT JOIN last_year_same_period ly ON ly.category = tp.category
ORDER BY yoy_pct DESC NULLS LAST;

Example 4: Rolling 13-Month Comparison

A more sophisticated pattern — for every month, compare to 12 months ago:

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.created_at)::DATE AS month,
        SUM(oi.quantity * oi.unit_price)         AS revenue
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    GROUP BY 1
),

with_yoy_lag AS (
    SELECT
        month,
        revenue,
        LAG(revenue, 12) OVER (ORDER BY month) AS revenue_12m_ago
    FROM monthly_revenue
)

SELECT
    month,
    ROUND(revenue, 2)            AS revenue,
    ROUND(revenue_12m_ago, 2)    AS revenue_12m_ago,
    ROUND(revenue - revenue_12m_ago, 2) AS yoy_delta,
    ROUND(100.0 * (revenue - revenue_12m_ago) / NULLIF(revenue_12m_ago, 0), 1) AS yoy_pct,
    CASE
        WHEN revenue > revenue_12m_ago * 1.1 THEN 'Growing'
        WHEN revenue < revenue_12m_ago * 0.9 THEN 'Declining'
        ELSE 'Stable'
    END AS trend
FROM with_yoy_lag
WHERE revenue_12m_ago IS NOT NULL
ORDER BY month;

Example 5: Stock Return Comparison — Relative to Index (StockMarket)

Compare each stock's return to the sector average (as a proxy for an index):

WITH monthly_stock_returns AS (
    SELECT
        sp.company_id,
        DATE_TRUNC('month', sp.price_date)::DATE AS month,
        ROUND(
            100.0 * (MAX(sp.close_price) - MIN(sp.close_price)) / NULLIF(MIN(sp.close_price), 0),
            2
        ) AS monthly_return
    FROM stock_prices sp
    GROUP BY sp.company_id, 2
),

sector_avg_return AS (
    SELECT
        msr.month,
        c.sector,
        AVG(msr.monthly_return) AS sector_avg
    FROM monthly_stock_returns msr
    JOIN companies c ON c.company_id = msr.company_id
    GROUP BY msr.month, c.sector
)

SELECT
    c.ticker,
    c.sector,
    msr.month,
    msr.monthly_return,
    ROUND(sar.sector_avg, 2)                   AS sector_avg_return,
    ROUND(msr.monthly_return - sar.sector_avg, 2) AS alpha
FROM monthly_stock_returns msr
JOIN companies c ON c.company_id = msr.company_id
JOIN sector_avg_return sar
    ON sar.month  = msr.month
    AND sar.sector = c.sector
ORDER BY msr.month, alpha DESC;

Purchase this course to unlock the full lesson.

← Previous Next →