SQL for Analysis · 14 min · 170 XP
CTEs and numbers you can trust
Build a query in named steps, and stop joins from quietly double-counting.
This course picks up where SQL Foundations stopped, on the same outdoor-gear shop: 8 customers, 8 products, 15 orders and 24 item lines. It assumes you can already filter, join and group.
customers (id, name, city, country, signup_date) products (id, name, category, price) orders (id, customer_id, order_date, status, shipping) order_items (order_id, product_id, quantity, unit_price)
The 11 paid orders11 rows
| order_id | customer | shipping | item_lines |
|---|---|---|---|
| 101 | Maja Berg | 5 | 2 |
| 102 | Liam Walsh | 0 | 1 |
| 104 | Sofia Rossi | 0 | 3 |
| 105 | Noah Keller | 5 | 1 |
| 106 | Aisha Khan | 0 | 2 |
| 108 | Maja Berg | 5 | 2 |
| 109 | Lucas Silva | 0 | 2 |
| 110 | Sofia Rossi | 5 | 1 |
| 112 | Emma Novak | 0 | 2 |
| 113 | Noah Keller | 5 | 1 |
| 114 | Maja Berg | 0 | 3 |
The query
SELECT o.id AS order_id, c.name AS customer, o.shipping, COUNT(*) AS item_lines FROM orders AS o JOIN customers AS c ON c.id = o.customer_id JOIN order_items AS i ON i.order_id = o.id WHERE o.status = 'paid' GROUP BY o.id, c.name, o.shipping ORDER BY o.id;
Name each step with WITH
Real questions take several steps. A CTE (common table expression) gives each step a name with WITH, so the query reads top to bottom like a recipe, and you can run each step on its own to check it.
WITH paid AS ( SELECT id, customer_id, order_date FROM orders WHERE status = 'paid' ) SELECT customer_id, COUNT(*) AS paid_orders FROM paid GROUP BY customer_id;
Grain: what one row means
The grain of a table, or of a query's result, is what one row stands for. In orders it's one order. In order_items it's one line of an order: one product, in some quantity. Join the two and the result takes the finer grain: one row per item line, with the order's columns copied onto each of its lines.
That's the trap SQL Foundations warned about in its joins lesson. Shipping is charged once per order, but after the join it sits on every line of that order. Orders 101 and 108 have two lines each:
| order_id | product | quantity | shipping |
|---|---|---|---|
| 101 | Trail Runner Shoes | 1 | 5 |
| 101 | Wool Socks (3-pack) | 2 | 5 |
| 108 | Headlamp | 1 | 5 |
| 108 | Water Bottle | 1 | 5 |
The query
SELECT o.id AS order_id, p.name AS product, i.quantity, o.shipping FROM orders AS o JOIN order_items AS i ON i.order_id = o.id JOIN products AS p ON p.id = i.product_id WHERE o.id IN (101, 108) ORDER BY o.id, p.name;
Each order paid 5.0 once, but each fee now appears twice. Sum that column and you've charged those two orders 20.0 instead of 10.0.
SELECT SUM(o.shipping) AS shipping_collected FROM orders AS o JOIN order_items AS i ON i.order_id = o.id WHERE o.status = 'paid'; -- shipping_collected -- 35.0
The paid orders actually charged 25.0 in shipping. The extra 10.0 is the second copy of the fees on orders 101 and 108. Nothing errors and nothing looks odd; the number is simply wrong. This is the most common way analysis goes quietly wrong.
Fix the grain before you join
The fix is to bring both sides to the same grain before joining. Total the items per order in a CTE, so there's exactly one row per order, and join that instead.
WITH order_totals AS ( SELECT order_id, SUM(quantity * unit_price) AS items_total FROM order_items GROUP BY order_id ) SELECT SUM(t.items_total) AS items, SUM(o.shipping) AS shipping FROM orders AS o JOIN order_totals AS t ON t.order_id = o.id WHERE o.status = 'paid'; -- items shipping -- 1628.0 25.0
Three habits catch this before anyone else does. Know what one row of each step means (one row per order). Count the rows before and after every join. And check at least one total against a figure you already trust.
Loading your workspace…