Skip to content

Window functions · Medium

How do you calculate a running total of monthly revenue?

Short answer

First aggregate revenue to one row per month, then apply SUM(revenue) OVER (ORDER BY month) to the result. With an ORDER BY inside OVER, the window runs from the first row up to the current row, giving a cumulative total. Add PARTITION BY to restart the running total for each year, region or category.

Detailed answer

WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date)  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 NOT IN ('cancelled', 'returned')
  GROUP BY month
)
SELECT month,
       revenue,
       SUM(revenue) OVER (ORDER BY month)                                  AS running_total,
       SUM(revenue) OVER (PARTITION BY substr(month, 1, 4) ORDER BY month) AS year_to_date
FROM monthly
ORDER BY month;

Frame detail that impresses interviewers: with ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the ORDER BY value are summed together. Because we aggregated to one row per month first there are no ties, but on raw daily data with duplicate dates you may want ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

A moving average uses the same idea with a bounded frame: AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) gives a three-month rolling average.