Intermediate Project: A Monthly Sales Report
Build a four-part sales report with CTEs, window functions, CASE and EXCEPT, then check every figure two ways.
In this lesson you will
- Combine CTEs, windows, CASE and sets in one report
- Keep one definition of revenue
- Check results two ways
- Plan the Advanced level
About 30 minutes
The monthly report Dev asked for
Maya’s cousin Dev needs a report for the shop’s end-of-quarter meeting. It must show revenue by month with a running total, the change from the previous month and each month’s share, then rank the customers and give them a tier, then name the best seller in each category, and finally list customers without a shipped order who deserve a nudge. Four requests, and each uses ideas from the last ten lessons. This project is the Intermediate level in miniature. As you read, try to guess which tool each result needs before looking at the query.
The plan
- Monthly sales. A CTE for order totals, a CTE for months, then window functions for the running total, change and share.
- Customer ranking. A CTE for spend, RANK for the position and CASE for the tier.
- Best seller per category. A CTE for units, ROW_NUMBER partitioned by category.
- Customers without a shipped order. A set operation, EXCEPT, on two simple lists.
Only shipped orders count as revenue, as in lesson 10. Always keep that rule in one place, in the first CTE, so every figure agrees.
The four queries, run for real
-- 1
WITH order_totals AS (
SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id GROUP BY o.id),
monthly AS (SELECT strftime('%Y-%m', order_date) AS month, SUM(total) AS revenue
FROM order_totals WHERE status = 'shipped' GROUP BY month)
SELECT month, ROUND(revenue, 2) AS revenue,
ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running,
ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS percent
FROM monthly ORDER BY month
-- 2
WITH order_totals AS (
SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id GROUP BY o.id),
spend AS (SELECT c.name, SUM(t.total) AS spent FROM order_totals AS t
JOIN customers AS c ON c.id = t.customer_id WHERE t.status = 'shipped' GROUP BY c.id)
SELECT name, ROUND(spent, 2) AS spent, RANK() OVER (ORDER BY spent DESC) AS position,
CASE WHEN spent >= 100 THEN 'top' WHEN spent >= 60 THEN 'mid' ELSE 'low' END AS tier
FROM spend ORDER BY position
-- 3
WITH units AS (
SELECT p.category, p.name, SUM(oi.qty) AS sold
FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id WHERE o.status = 'shipped' GROUP BY p.id),
ranked AS (SELECT category, name, sold,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sold DESC, name) AS rn FROM units)
SELECT category, name, sold FROM ranked WHERE rn = 1 ORDER BY category
-- 4
SELECT id, name FROM customers
EXCEPT SELECT c.id, c.name FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'shipped' ORDER BY 1
-- 1
month | revenue | running | change | percent
2026-06 | 65.50 | 65.50 | NULL | 25.50
2026-07 | 33.00 | 98.50 | -32.50 | 12.80
2026-08 | 54.00 | 152.50 | 21.00 | 21.00
2026-09 | 41.50 | 194.00 | -12.50 | 16.10
2026-10 | 63.00 | 257.00 | 21.50 | 24.50
-- 2
name | spent | position | tier
Asha | 116.50 | 1 | top
Dev | 89.50 | 2 | mid
Ben | 51.00 | 3 | low
-- 3
category | name | sold
care | Plant food | 4
plant | Fern | 6
pot | Clay pot | 6
-- 4
id | name
3 | Chloe
5 | Ella
6 | Femi
Query 1 is the heart of the report. The strongest month was 2026-06 at 65.50, and the weakest was 2026-07 at 33.00. The running total reaches 257.00, matching the shipped revenue from the earlier lessons. Revenue rose in 2 months and fell in 2, and the first month has no change because there is no month before it. The shares add up to roughly a hundred per cent, with a small rounding difference. Query 2 puts Asha first at 116.50 in the ‘top’ tier, then Dev in ‘mid’ and Ben in ‘low’. The tier thresholds, 100 and 60, are a business decision, and the data contains no values exactly on them. Query 3 shows the best seller of each category: Plant food (care, 4 units), Fern (plant, 6 units), Clay pot (pot, 6 units). Query 4 uses EXCEPT to list the 3 customers with no shipped order: Chloe, Ella, Femi. Two of them ordered but nothing shipped, and one has never ordered, which is useful for deciding who to contact.
Checking the report
- Totals agree. The last running total must equal the sum of the months and the shipped revenue.
- Edge cases. What happens to a month with no sales? It simply does not appear. If you need it to show as zero, generate the months with a recursive CTE and join.
- Ties. Queries 2 and 3 have tie-breakers, so the answer is the same each time.
- Boundaries. Check which tier a customer on exactly 60 would fall into.
- Dialect. The month function here is SQLite’s. Replace it with your own database’s equivalent.
Where this leads
You can now break a problem into CTEs, make decisions with CASE, rank and accumulate with window functions, combine lists with set operations and design tables with constraints and indexes. The Advanced level is about working at scale and in a job: query planning in depth, transactions and concurrency, working with messy data, designing a whole database, reading other people’s queries and handling real projects.
Presenting a report whose figures you have not cross-checked. A query can run and still count the wrong orders. Compare the totals two ways before anything is shown to other people.
The shop, customers and sales are made up, in sample money. The queries ran in SQLite, and the month function in particular differs between databases. Tier names and thresholds are examples, not advice.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Why is the shipped-only rule placed in the first CTE?
- So every figure in the report uses the same definition of revenue
- CTEs cannot use WHERE
- To make the query shorter
- To speed up the index
One place means one definition.
Which tool produced the running total in query 1?
- GROUP BY alone
- SUM with a window ordered by month
- UNION
- A view
It ends at 257.00.
Which tool assigned the tiers in query 2?
- EXCEPT
- LAG
- DISTINCT
- CASE
CASE chose top, mid or low from the spend.
Why does query 3 use ROW_NUMBER?
- To rank ties together
- To keep exactly one best seller per category
- To remove NULLs
- To join tables
Partitioned by category, ROW_NUMBER 1 is the best seller.
What does EXCEPT do in query 4?
- Adds customers
- Joins customers to orders
- Sorts customers
- Removes customers with a shipped order from the list of all customers
It leaves customers with no shipped order.
Summary
- Break the report into named steps.
- Window functions give totals, changes and ranks without losing rows.
- CASE and EXCEPT finish the story.
- Cross-check totals before sharing.
Questions
Can I write this report with fewer queries?
Yes, many ways exist. Four separate queries keep each part easy to check. Combine them later if you need a single result.
How do I show months with no sales?
Generate the full list of months, for example with a recursive CTE, and LEFT JOIN the sales to it.
Are these figures real?
No. The shop and its sales are made up, in sample money.
