Cumulative Revenue by Month — Running Totals in SQL

The problem

Scenario: Brightlane's finance team is tracking cumulative revenue growth month by month.

Task: Write a query to return each order month, the total orders revenue for that month, and the running total of all orders revenue from the earliest month through that month.

Assumptions:

An order month is identified by its first day and covers every order placed within that month.
A month's monthly_revenue is the combined total_amount across orders placed in that month.
A month's cumulative_revenue is the combined monthly_revenue from the earliest month through that month inclusive.

Output:

One row per order month present in the data.
Columns in this order: order_month, monthly_revenue, cumulative_revenue.
Sorted by order_month ascending.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  DATE_TRUNC('month', ordered_at) AS order_month,
  SUM(total_amount) AS monthly_revenue,
  SUM(SUM(total_amount)) OVER (
    ORDER BY
      DATE_TRUNC('month', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
  ) AS cumulative_revenue
FROM
  orders
GROUP BY
  DATE_TRUNC('month', ordered_at)
ORDER BY
  order_month

The shape

Aggregate revenue to the month first, then accumulate. SUM(total_amount) collapses each month's orders into a single monthly revenue figure, and SUM(SUM(total_amount)) OVER (ORDER BY ...) adds those monthly figures together in date order. The result is one row per month with that month's revenue and the running total through that month.

Clause by clause

SELECT DATE_TRUNC('month', ordered_at) AS order_month, SUM(total_amount) AS monthly_revenue produces the per-month revenue row that the window function then accumulates across. Each order in February 2022 truncates to 2022-02-01 and contributes to that month's monthly_revenue.
SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue runs forward through the months. The inner SUM(total_amount) is the per-month revenue. The outer SUM(...) OVER (...) sums those monthly figures from the earliest month through the current month.
FROM orders GROUP BY DATE_TRUNC('month', ordered_at) aggregates the raw orders to one row per month so the window has clean monthly rows to accumulate over.
ORDER BY order_month sorts the final output chronologically.

Why the frame matters even on aggregated data

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the explicit form of the default frame for an ordered window. Writing it out makes the running-total intent obvious in the SQL: the frame starts at the earliest month and grows by one month at each step. Skipping the frame clause produces the same result here (because the implicit RANGE default and the explicit ROWS frame coincide on one-row-per-period data), but stating the frame removes ambiguity about how the accumulation behaves.

You practiced layering an unbounded-preceding window over a per-month revenue summary so each month carries both its own revenue and the running total through that point.

Return each order month, the total `orders` revenue for that month, and the running total of all `orders` revenue from the earliest month through that month

The shape

Clause by clause

Why the frame matters even on aggregated data

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.