Month Over Month Revenue Change — Period-over-Period in SQL

The problem

Scenario: Brightlane's CFO dashboard requires a month-over-month revenue report that shows how much revenue changed from the prior month, not just the prior month's value.

Task: Write a query to return each calendar month, the total orders revenue for that month, and the difference between that month's revenue and the immediately preceding month's revenue.

Assumptions:

A calendar month is identified by its first day and covers every order placed within that month.
The revenue_change value on each month is the current month's revenue minus the immediately preceding month's revenue.
The earliest month in the data has no preceding month; its revenue_change value is missing.

Output:

One row per calendar month present in the data.
Columns in this order: month (the first day of the calendar month), revenue, revenue_change.
Sorted by 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)::date AS MONTH,
  SUM(total_amount) AS revenue,
  SUM(total_amount) - LAG(SUM(total_amount)) OVER (
    ORDER BY
      DATE_TRUNC('month', ordered_at)
  ) AS revenue_change
FROM
  orders
GROUP BY
  DATE_TRUNC('month', ordered_at)
ORDER BY
  MONTH

The shape

The prior-month value and the current month's value have to land on the same row before the arithmetic can run, so LAG(SUM(total_amount)) does the alignment and SUM(total_amount) - LAG(SUM(total_amount)) is the difference taken inline. The composition is window-over-aggregate followed by row-level subtraction.

Clause by clause

SELECT DATE_TRUNC('month', ordered_at)::date AS month, SUM(total_amount) AS revenue, SUM(total_amount) - LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', ordered_at)) AS revenue_change returns the month bucket, the month's revenue, and the month-over-month change. The subtraction happens row by row, between the current row's SUM and the value LAG pulls from the prior row.
FROM orders reads every order in the table.
GROUP BY DATE_TRUNC('month', ordered_at) reduces the rows to one row per calendar month so each SUM(total_amount) is a monthly total.
ORDER BY month prints the months in calendar order.

Why the subtraction is inline and not in a second query

The window runs at the same evaluation step as the rest of the SELECT list, so the previous month's revenue is available as an expression the moment the current row is built. A second query reading the first one's output would do the same thing in two passes — it is not wrong, but the inline form is one statement, one scan, and the relationship between the two numbers is visible right where it is computed.

The trap

The first month in the data has no prior row in the window, so the subtraction is revenue - NULL, which is itself NULL. The revenue_change column reports NULL for the earliest month rather than the current month's revenue. That is the correct answer — there is no prior month to subtract — but downstream code that sums or averages this column will silently propagate the NULL unless it explicitly handles the boundary case.

You practiced subtracting LAG'd monthly revenue from the current monthly revenue to express month-over-month change as an inline value.

Return each calendar month, the total `orders` revenue for that month, and the difference between that month's revenue and the immediately preceding month's revenue

The shape

Clause by clause

Why the subtraction is inline and not in a second query

The trap

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