Unit 06.00: Building a query in testable stages

Unit ID: SQL-M06-U01 - Estimated active time: 13-16 minutes Objective: assemble a multi-step query so that each step is verified before the next is added.

Write it wrong once and you will debug it forever

The tempting approach is to write the whole forty-line query, run it, and squint at the answer. When it is wrong - and the first version usually is - you have no idea which part failed.

The alternative costs a minute and saves an afternoon: build it in stages and check the row count after each.

Stage by stage

Stage 1 - the population. Get the rows, check the count.

SELECT COUNT(*) FROM orders WHERE status = 'completed';
-- 988

Stage 2 - add the measure. Count must not change.

SELECT COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders WHERE status = 'completed';
-- 988 | 2685905.00

Stage 3 - add the join. Now the count *will* change, and you predict it first.

SELECT COUNT(*)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed';
-- 988

Predicted 988 - one customer per order, so no fan-out. Confirmed. Only now is it safe to aggregate.

Stage 4 - group. The grain changes deliberately.

SELECT c.country, COUNT(*) AS orders, SUM(o.order_total) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.country
ORDER BY revenue DESC;

Four stages, four checks, and if stage 3 had returned 3,400 you would have caught fan-out before it reached a total.

The habit that makes this cheap

Keep COUNT(*) in the query while you build and remove it at the end. It costs nothing and it is the single most informative column during construction.

Practice

Build, in stages, a query giving revenue per city for completed orders. Write down the count you expect after each stage before running it.

-- 1. population
SELECT COUNT(*) FROM orders WHERE status = 'completed';                    -- 988

-- 2. join (predict: still 988, one customer per order)
SELECT COUNT(*) FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed';                                              -- 988

-- 3. group (predict: one row per distinct city)
SELECT LOWER(TRIM(c.city)) AS city, COUNT(*) AS orders, SUM(o.order_total) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY 1
ORDER BY revenue DESC;

Takeaway

Add one stage at a time and check the row count against a prediction. A query built this way is already half-verified by the time it is finished.

---

Mark complete