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.

The four tables
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_idcustomershippingitem_lines
101Maja Berg52
102Liam Walsh01
104Sofia Rossi03
105Noah Keller51
106Aisha Khan02
108Maja Berg52
109Lucas Silva02
110Sofia Rossi51
112Emma Novak02
113Noah Keller51
114Maja Berg03
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.

sql
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:

Orders 101 and 108, joined to their lines
order_idproductquantityshipping
101Trail Runner Shoes15
101Wool Socks (3-pack)25
108Headlamp15
108Water Bottle15
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.

Looks reasonable. It's wrong.
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.

sql
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…