Capstone: find the six wrong queries

The capstone audit

The project ships ten queries in queries/audit-lab/, named q01.sql through q10.sql. Every one runs without error and returns a tidy-looking answer, and each carries its business question in a comment at the top. Exactly six of the ten are wrong. The other four are correct, and that matters as much - someone who flags all ten has learned suspicion, not auditing. Part of the skill is leaving a correct query alone.

For each query, give a verdict: correct or wrong. If it is wrong, name the failure class, write a corrected query, record both the wrong number and the right one, and note what one row represented before and after your fix.

One warning. docs/known-defects.md describes what is deliberately wrong with the data, which is a different question from what is wrong with these queries. Reading it hoping for verdicts will mislead you. The answer key is at the bottom of this page, deliberately not in the repository, so you cannot open it by accident before you have verdicts.

The failure classes

Every wrong query fails in one identifiable way, and each of these six appears exactly once:

A join fan-out that inflates an order-header measure.
A != or IN filter that silently drops NULL order_status.
The wrong revenue column, unit_price where net_price was meant.
A BETWEEN on a timestamp column that drops the last day.
An INNER JOIN to a dimension that drops rows with orphan keys.
A DISTINCT that hides a duplicated dimension row.

Your task

Run each query, then walk the seven-point checklist from "Audit what it wrote" over it. Copy queries/audit-lab/findings-template.md to queries/audit-lab/findings.md and write one section per query: the verdict, the failure class if wrong, the corrected query, both numbers, and what one row represented before and after your fix. Do not change the query files themselves.

Rubric

Score yourself out of 10. Ten out of ten means the capstone meets the course requirements.

Area	Points	Evidence
Correct verdicts	3	All ten classified correctly, including the four that are right
Failure naming	2	Each wrong query labelled with the failure class that actually applies
Corrected queries	2	Each fix runs and returns the right number
Both numbers stated	1	Wrong number and right number recorded for each finding
Reasoning quality	1	Each finding says what one row represented before and after the fix
No false positives	1	The four correct queries are not "fixed"

Optional extensions

Finish the spine question's customer half: audit the join to customers that finds which customers drove the growth in the winning categories, using what q09 taught you to check.
Ask the agent to audit the same ten queries, then compare its findings to yours. Where you disagreed, who was right?
Write a Bruin quality check that would have caught one of the six automatically.
Add the failure you found hardest to AGENTS.md, then see whether the agent avoids it next time.

Check your understanding

How many of the ten queries are wrong?
Why do the four correct queries matter as much as the six wrong ones?
Name three of the six failure classes.

Do it with your agent

Say next lesson and your agent teaches this, asks you these questions, then sets the task above. Do it by hand, then say review my work - it checks your work against a rubric and tells you what to fix or marks the lesson done.

Warning

Spoilers for the entire lab. Commit to a verdict on all ten queries in findings.md before reading further.

Answer key

The six wrong queries are q02, q04, q05, q07, q08, q09. The four correct ones are q01, q03, q06, and q10. Every failure class appears exactly once, and all values are exact, because the data generates identically on every machine.

Query	Verdict	As written	Correct	Why
q01	correct	1,200	1,200	Plain `COUNT(*)` on one table; nothing to fan out.
q02	wrong	London 261,246.42	London 102,911.39	`order_total` is order-header; the join to `order_items` repeats each order per line. Sum from `orders` alone.
q03	correct	Paris 190	Paris 190	Join to `stores` only adds a city name; grain unchanged.
q04	wrong	1,069	1,093	`!= 'cancelled'` drops 24 NULL-status orders. Use `IS DISTINCT FROM`.
q05	wrong	381,357.00	338,209.56	`unit_price` is catalogue price; revenue is `quantity * net_price`.
q06	correct	503.39	503.39	`AVG(order_total)` over `orders` alone; right denominator.
q07	wrong	478	480	`ordered_at` is a timestamp; `BETWEEN ... AND '2024-12-31'` stops at midnight and drops two later-day orders. Use a half-open range.
q08	wrong	847,979.80 across 8 categories	851,617.69 incl. Unknown bucket	15 lines point at a `product_id` not in `products`; the INNER JOIN drops them and 3,637.89.
q09	wrong	1,411.16	1,310.60	Ten duplicated rows in `customers` repeat those orders in the join; `COUNT(DISTINCT customer_id)` stays correct, which is why it looks safe.
q10	correct	2.4	2.4	Lines divided by distinct orders, both from `order_items` at its own grain.

The fan-out factor is exactly 2.4 table-wide but ranges about 2.31 to 2.54 per store, because orders with more lines are not spread evenly, so a per-store ratio near but not exactly 2.4 is correct, not an arithmetic error.

Capstone: find the six wrong queries

Sign up to our newsletter