Monthly Revenue vs Previous Month — Period-over-Period in SQL

The problem

Scenario: Brightlane's finance team is preparing a monthly revenue summary and wants each month's total displayed alongside the prior month's total for easy comparison.

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

Assumptions:

A calendar month is identified by its first day and covers every order placed within that month.
The earliest month in the data has no preceding month; its prev_month_revenue 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, prev_month_revenue.
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,
  LAG(SUM(total_amount)) OVER (
    ORDER BY
      DATE_TRUNC('month', ordered_at)
  ) AS prev_month_revenue
FROM
  orders
GROUP BY
  DATE_TRUNC('month', ordered_at)
ORDER BY
  MONTH

The shape

LAG(SUM(total_amount)) reaches one row back in the same query that produced the monthly totals, attaching each month's prior-month revenue as a column on the current month's row. The aggregation defines the row grain — one row per calendar month — and the window function looks back across that grain.

Clause by clause

SELECT DATE_TRUNC('month', ordered_at)::date AS month, SUM(total_amount) AS revenue, LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', ordered_at)) AS prev_month_revenue returns three columns per month: the month bucket, the month's revenue, and the previous month's revenue. The ::date cast strips the timestamp off the truncation result so the column reads as a plain calendar date. LAG is called with no offset, so it reaches back exactly one row in the ordered sequence.
FROM orders reads every order in the table; nothing is filtered out.
GROUP BY DATE_TRUNC('month', ordered_at) collapses the rows into one row per month, which is what makes SUM(total_amount) a per-month total.
ORDER BY month prints the months in calendar order so the prior-month column reads naturally next to its current month.

The trap

LAG runs after the aggregation, not over the raw orders rows. The window operates on the result of the GROUP BY, which is why LAG(SUM(total_amount)) is the right syntax rather than SUM(LAG(total_amount)). The first month in the data has no prior row in the window, so prev_month_revenue is NULL for February 2022 — that NULL is informative, not a defect.

You practiced using LAG over monthly revenue totals to attach each month's prior-month value alongside the current value as an inline column.

Return each calendar month, the total `orders` revenue for that month, and the total `orders` revenue for the immediately preceding calendar month

The shape

Clause by clause

The trap

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