Monthly Numbers
Orders, cancellations, revenue.
Now we group the order totals by month. We want three numbers per month. Orders, cancellations, and revenue. And only real revenue, so cancelled and returned orders don't count.

- Group by month
- Three numbers
- Only real revenue
Step 2: group by month
The CTE stays the same. Below it, we group by month. Count star gives the orders. Case inside sum counts the cancellations. And for revenue, case keeps the total only for delivered and shipped orders, so the rest are ignored. Run it. August had the most revenue, at about three thousand four hundred dollars. December had sixteen cancellations, the most of any month.
WITH order_totals AS (
SELECT o.order_id, o.order_date, o.status,
SUM(oi.quantity * b.price) AS total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
GROUP BY o.order_id
)
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
ROUND(SUM(CASE WHEN status IN ('delivered', 'shipped')
THEN total END), 2) AS revenue
FROM order_totals
WHERE order_date >= '2025-07-01'
GROUP BY month;โโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโ โ month โ orders โ cancelled โ revenue โ โโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโค โ 2025-07 โ 77 โ 11 โ 2196.23 โ โ 2025-08 โ 120 โ 8 โ 3423.57 โ โ 2025-09 โ 93 โ 9 โ 2593.02 โ โ 2025-10 โ 93 โ 7 โ 2638.96 โ โ 2025-11 โ 83 โ 6 โ 2523.04 โ โ 2025-12 โ 104 โ 16 โ 2569.5 โ โโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโ
Step 3: compare months
Now we add two more CTEs. Monthly works out revenue per month. Changes uses lag to bring in last month's revenue, next to this month's. Then the percentage change is, this month minus last month, divided by last month, times one hundred. We filter at the very end, so July can still see June. Run it. August revenue jumped by about fifty six percent. September fell back by about twenty four percent.
WITH order_totals AS (
SELECT o.order_id, o.order_date, o.status,
SUM(oi.quantity * b.price) AS total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
GROUP BY o.order_id
),
monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
SUM(CASE WHEN status IN ('delivered', 'shipped')
THEN total END) AS revenue
FROM order_totals
GROUP BY month
),
changes AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS last_month
FROM monthly
)
SELECT month,
ROUND(revenue, 0) AS revenue,
ROUND(100.0 * (revenue - last_month) / last_month, 1) AS pct_change
FROM changes
WHERE month >= '2025-07';โโโโโโโโโโโฌโโโโโโโโโโฌโโโโโโโโโโโโโ โ month โ revenue โ pct_change โ โโโโโโโโโโโผโโโโโโโโโโผโโโโโโโโโโโโโค โ 2025-07 โ 2196.0 โ -12.0 โ โ 2025-08 โ 3424.0 โ 55.9 โ โ 2025-09 โ 2593.0 โ -24.3 โ โ 2025-10 โ 2639.0 โ 1.8 โ โ 2025-11 โ 2523.0 โ -4.4 โ โ 2025-12 โ 2570.0 โ 1.8 โ โโโโโโโโโโโดโโโโโโโโโโดโโโโโโโโโโโโโ
Summary
Group by month gives the numbers, and lag gives the change. That's the heart of the monthly report. Last step, the best sellers.
Key takeaways- GROUP BY month
- CASE for cancellations
- LAG for % change