Running revenue by month

Finance wants a cumulative revenue line: each month’s delivered revenue, plus the total so far.

Revenue for a line item is quantity * unit_price. Only count orders whose status is 'delivered'. A month is the calendar month of order_date; report it as a date on the first of the month (for example 2025-03-01).

Return one row per month that has delivered revenue:

column	type	meaning
month	date	first day of the month
revenue	decimal	delivered revenue in that month, rounded to 2 decimals
running_revenue	decimal	delivered revenue from the first month up to and including this one, rounded to 2 decimals

Months with no delivered orders don’t appear. Order matters: oldest month first.

Tables: orders, order_items.

Approach

Aggregate to one row per month, then run a cumulative SUM over those rows in month order.

WITH monthly AS (
  SELECT CAST(date_trunc('month', o.order_date) AS 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 = 'delivered'
  GROUP BY 1
)
SELECT month,
       ROUND(revenue, 2) AS revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS running_revenue
FROM monthly
ORDER BY month;

Why it works

A window function keeps every row and adds a value computed from its neighbours. ORDER BY month inside OVER defines who the neighbours are. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW says “from the first row up to me”, which is exactly a running total.

That frame is also the default when you write only ORDER BY (strictly, the default is RANGE, which treats rows with equal sort keys as one step). Months are unique here, so both give the same answer. Spelling out ROWS makes the intent plain.

Round at the end. Rounding each month and then summing the rounded values can drift by a cent.

Under the hood

DuckDB sorts the monthly rows by month (two years, so 24 rows) and walks them once, keeping a single running sum: add the current row, emit, move on. For frames that don’t only grow, like “the last 3 months”, it builds a segment tree over the partition so each frame’s sum is O(log n) instead of re-adding every row.