SQL / CAPSTONE PROJECTS
Project: an analytics report over a shop database
Build a monthly analytics report over a shop schema using layered CTEs at explicit grains, a month spine for empty periods, and window totals that reconcile.
What you will learn
- Name the grain of every CTE and confirm no join changes it
- Pre-aggregate each one-to-many table on its own before joining, to stop fan-out
- LEFT JOIN aggregates onto a generated month spine and COALESCE the gaps to 0
- Get several measures in one pass with SUM(CASE WHEN ...) and COUNT(DISTINCT CASE ...)
Understanding Project: an analytics report over a shop database
Every column in a report is a measure at a grain — the thing one input row stands for. order_items is at item grain, so summing qty * unit_price_cents there is revenue only if each item row appears exactly once. A join is the one place the grain changes without you asking for it: bring in a second table with several matching rows per order and every item row is repeated, and SUM will faithfully add the duplicates. Writing the report as one CTE per grain — item, then order, then month — makes that failure visible, because you can count rows at each level and see where the count jumps.
The second structural decision is which rows the report is allowed to contain. Aggregating the fact table can only produce keys that exist in it, so a month with no paid orders does not come out as zero, it comes out as nothing at all, and a chart draws a straight line over the hole. Generating the reporting keys first — a list of months, a list of products — and LEFT JOINing the aggregates onto them fixes that, provided every filter on the fact side lives inside the aggregating CTE or in the ON clause; a WHERE status = 'paid' placed after the LEFT JOIN throws the empty months straight back out, because NULL = 'paid' is not true.
Define the measures in words before writing SQL: which statuses count as revenue, whether a refund reduces the month it happened in or the month of the original sale, and what the denominator of each percentage is. Window functions belong in the last layer, because they run after GROUP BY and therefore see the report's own rows: SUM(cents) OVER (ORDER BY month) is a running total of the monthly figures, and SUM(cents) OVER () is the report's grand total, which is the only correct denominator for a share. Finish by reconciling one number — the final running total against a single independent SUM over the fact table — since a report that is internally consistent but wrong is the normal result of fan-out.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
order_date TEXT NOT NULL,
status TEXT NOT NULL
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
product TEXT NOT NULL,
qty INTEGER NOT NULL,
unit_price_cents INTEGER NOT NULL
);
INSERT INTO orders VALUES
(1,'2024-01-05','paid'), (2,'2024-01-22','paid'),
(3,'2024-02-11','cancelled'), (4,'2024-04-02','paid'),
(5,'2024-04-19','paid'), (6,'2024-04-28','paid');
INSERT INTO order_items VALUES
(1,'mug',2,1250), (1,'poster',1,2000), (2,'mug',1,1250),
(3,'poster',5,2000),(4,'tote',3,1800), (5,'mug',4,1250),
(6,'poster',2,2000),(6,'tote',1,1800);
WITH months(month) AS (
VALUES ('2024-01'),('2024-02'),('2024-03'),('2024-04')
),
paid_order AS ( -- grain: one row per paid order
SELECT o.id,
substr(o.order_date,1,7) AS month,
SUM(i.qty * i.unit_price_cents) AS cents
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'paid'
GROUP BY o.id, substr(o.order_date,1,7)
),
per_month AS ( -- grain: one row per month that had sales
SELECT month, COUNT(*) AS paid_orders, SUM(cents) AS cents
FROM paid_order
GROUP BY month
)
SELECT m.month,
COALESCE(p.paid_orders, 0) AS paid_orders,
COALESCE(p.cents, 0) AS revenue_cents,
SUM(COALESCE(p.cents, 0)) OVER (ORDER BY m.month) AS running_cents
FROM months m
LEFT JOIN per_month p ON p.month = m.month
ORDER BY m.month;A report is a chain of aggregations at explicitly named grains, and any join that changes the grain multiplies your measures instead of erroring.
Worked examples
Fan-out inflates two measures at once
Joining items and payments to orders in one query doubles both totals, and the two wrong numbers still agree with each other.
CREATE TABLE ord (id INTEGER PRIMARY KEY);
CREATE TABLE items (order_id INTEGER, cents INTEGER);
CREATE TABLE payments (order_id INTEGER, cents INTEGER);
INSERT INTO ord VALUES (1),(2);
INSERT INTO items VALUES (1,3000),(1,2000),(2,1000);
INSERT INTO payments VALUES (1,2500),(1,2500),(2,1000);
WITH raw_join AS (
SELECT SUM(i.cents) AS item_cents, SUM(p.cents) AS paid_cents
FROM ord o
JOIN items i ON i.order_id = o.id
JOIN payments p ON p.order_id = o.id
),
item_tot AS (SELECT order_id, SUM(cents) AS cents FROM items GROUP BY order_id),
pay_tot AS (SELECT order_id, SUM(cents) AS cents FROM payments GROUP BY order_id),
fixed AS (
SELECT SUM(t.cents) AS item_cents, SUM(p.cents) AS paid_cents
FROM item_tot t
JOIN pay_tot p ON p.order_id = t.order_id
)
SELECT 'a: joined raw' AS method, item_cents, paid_cents FROM raw_join
UNION ALL
SELECT 'b: pre-aggregated' AS method, item_cents, paid_cents FROM fixed
ORDER BY method;Example explained
Line 1Order 1 has 2 item rows and 2 payment rows, so the double join emits 2 x 2 = 4 rows for it and each amount is added twice.
Line 2The inflated figures still balance (11000 = 11000), which is why fan-out survives the usual 'do the totals match?' check.
Line 3item_tot and pay_tot each collapse their own table to one row per order, so the final join is 1:1 and the sums are the true 6000.
Line 4ORDER BY method only fixes the display order of the UNION ALL; the arithmetic does not depend on it.
Share of total at the report's grain
A window function over already-aggregated rows gives each product's percentage of revenue with the correct denominator.
CREATE TABLE sales (product TEXT, cents INTEGER);
INSERT INTO sales VALUES
('mug',1250),('mug',2500),
('poster',2000),('poster',4000),
('tote',5400),('tote',1800);
WITH by_product AS (
SELECT product, SUM(cents) AS cents
FROM sales
GROUP BY product
)
SELECT product,
cents,
ROUND(100.0 * cents / SUM(cents) OVER (), 1) AS pct_of_total,
RANK() OVER (ORDER BY cents DESC) AS rnk
FROM by_product
ORDER BY cents DESC;Example explained
Line 1by_product reduces six raw sales rows to three product rows; that is the grain the report publishes.
Line 2SUM(cents) OVER () with no PARTITION BY sums those three rows, so the denominator is 16950, the report total, not a raw-row total.
Line 3RANK() sees the same post-GROUP BY rows, so ranking and totalling happen in one pass over the aggregate.
Line 4The 100.0 forces real division; 100 * cents / SUM(...) would use integer division and report 42 instead of 42.5.
Several measures in one pass
Conditional aggregation reports all orders, paid orders and distinct paying customers per month without repeating the query once per status.
CREATE TABLE orders_log (
id INTEGER, customer_id INTEGER, month TEXT, status TEXT, cents INTEGER
);
INSERT INTO orders_log VALUES
(1,10,'2024-01','paid', 4500),
(2,11,'2024-01','refunded', 1250),
(3,10,'2024-02','paid', 5400),
(4,12,'2024-02','paid', 5000),
(5,12,'2024-02','cancelled',2000);
SELECT month,
COUNT(*) AS all_orders,
SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid_orders,
SUM(CASE WHEN status='paid' THEN cents ELSE 0 END) AS paid_cents,
COUNT(DISTINCT CASE WHEN status='paid' THEN customer_id END) AS paying_customers
FROM orders_log
GROUP BY month
ORDER BY month;Example explained
Line 1COUNT(*) stays unfiltered, so all_orders can serve as the denominator of a paid-conversion rate; a WHERE status='paid' would destroy it.
Line 2SUM(CASE ... ELSE 0 END) restricts a measure without restricting the group, keeping every measure at month grain.
Line 3The CASE in COUNT(DISTINCT ...) has no ELSE, so non-paid rows become NULL and COUNT skips them.
Line 4Customer 12 has one paid and one cancelled order in February and is still counted once, because DISTINCT works on the surviving customer ids.
Important notes
Text month keys sort correctly only when zero-padded ISO, like '2024-09'; a hand-built '2024-9' sorts after '2024-10' and quietly scrambles the running total.
A month with no orders and a month whose orders were all cancelled both display 0 revenue here; if that difference matters, carry an unfiltered order count as its own column.
Common mistakes
Joining order_items and a second one-to-many table such as payments or shipments in the same query: both sums are multiplied by the same factor, so they still balance and the report looks trustworthy while every figure is too high.
Putting WHERE status = 'paid' after the LEFT JOIN to the month spine, which drops exactly the zero-revenue months the spine was added to keep, because NULL = 'paid' is not true.
Calling COUNT(*) the order count after joining orders to order_items: it counts item rows, so an order with four products counts four times, inflating orders and deflating average order value.
Try it yourself
Change, predict, then run
Extend the main query with prev_cents from LAG(COALESCE(p.cents,0)) OVER (ORDER BY m.month) and a delta_cents column, then check that the last running_cents still equals a plain SUM(qty * unit_price_cents) over paid orders only. Decide whether 2024-01 should show NULL or 0 for prev_cents and say why.
Open the SQL workspaceCheck your understanding
A monthly revenue report joins orders to order_items and also to shipments, then takes SUM(order_items.qty * unit_price_cents). Every March order has exactly 2 items and 2 shipments. What does March show?
- Correct revenue, because SUM ignores the duplicate rows the shipments join creates
- Half the true revenue, because each item's amount is split across its shipments
- Twice the true revenue, because every item row is repeated once per shipment
- An error, because two one-to-many joins cannot be grouped together
Show answer
Joining two independent one-to-many tables to orders produces their cross product per order: 2 items x 2 shipments = 4 rows, so each item amount is added twice and March comes out at 2x reality. The first option is tempting because SUM feels like it works on the set of items, but a join result is a multiset and SUM adds every row handed to it; the fix is to aggregate items and shipments separately into one row per order, then join those results.