Recap — Running Totals and Growth Rates

Every dashboard you'll ever ship has "% vs last period" somewhere. There are two ways to write this in SQL: the wrong way (self-joins on date offsets) and the right way (window functions).

Running totals

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue
FROM daily_metrics;

SUM(...) OVER (ORDER BY ...) is a running sum — each row gets the total up to and including that row.

Month-over-month growth with LAG

SELECT
    order_month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY order_month) AS prev_month_revenue,
    ROUND(100.0 * (monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY order_month))
        / LAG(monthly_revenue) OVER (ORDER BY order_month), 2) AS mom_pct
FROM monthly_revenue;

LAG(x) OVER (ORDER BY ...) gets the previous row's value of x. The MoM growth is (current - prev) / prev. Cast to 100.0 to avoid integer division.

YoY growth — LAG with an offset

SELECT
    order_month,
    monthly_revenue,
    LAG(monthly_revenue, 12) OVER (ORDER BY order_month) AS prev_year_revenue
FROM monthly_revenue;

The second arg to LAG is "how many rows back". For YoY on monthly data, that's 12.

Rolling 7-day / 28-day metrics

SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_metrics;

The ROWS BETWEEN ... PRECEDING AND CURRENT ROW clause defines a rolling window. 7-day rolling avg = 6 preceding + current = 7 rows.

The gotcha: missing dates

LAG and rolling windows assume rows are contiguous. If you have no orders on Sunday, the "previous row" is Saturday's, not Sunday's. Two fixes:

Use a date spine — generate a series of dates and LEFT JOIN your metrics:

WITH date_spine AS (
    SELECT generate_series('2026-01-01'::date, '2026-12-31'::date, '1 day'::interval)::date AS d
)
SELECT
    s.d,
    COALESCE(m.daily_revenue, 0) AS daily_revenue,
    SUM(COALESCE(m.daily_revenue, 0)) OVER (ORDER BY s.d) AS cumulative
FROM date_spine s
LEFT JOIN daily_metrics m ON m.order_date = s.d;

Use range-based window frames — RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW instead of ROWS BETWEEN 6 PRECEDING .... Slower but date-aware.

Common interview asks

Top product per month → window + RANK()
Customer's nth purchase → window + ROW_NUMBER()
Time between purchases → window + LAG() on order_date
Compare current quarter to same quarter last year → LAG(revenue, 4) OVER (ORDER BY quarter)

Takeaway

If you find yourself self-joining a table to itself to compare adjacent rows, stop. Use LAG or LEAD. They're faster, clearer, and what interviewers expect to see.