Recap — Running Totals and Growth Rates

# 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 ```sql 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 ```sql 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 ```sql 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 ```sql 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: 1. **Use a date spine** — generate a series of dates and LEFT JOIN your metrics: ```sql 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; ``` 2. **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.