Capstone: find the six wrong queries

The task

The project ships ten queries in queries/audit-lab/, named q01.sql through q10.sql. Every one of them runs without error and returns a number. Exactly six of the ten return the wrong answer. Each query has its business question in a comment at the top.

Your job, for each query: decide whether the answer is correct or wrong. If it is wrong, name the failure class, write a corrected query, and record both the wrong number and the right one.

The four correct queries matter as much as the six wrong ones. A student who flags all ten has not learned to audit. They have learned to be suspicious, which is a different and less useful thing. Part of the skill is leaving a correct query alone.

The failure classes you are looking for

Every wrong query fails in one identifiable way, and all six are faults this course has already taught you to spot:

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.

Run each query, then walk the seven-point checklist from "Audit what it wrote" over it. The fault, when there is one, will be one of the six above.

Record your findings

Copy queries/audit-lab/findings-template.md to queries/audit-lab/findings.md and write your findings there, one section per query. For each query state the verdict, the failure class if wrong, the corrected query, both numbers, and what one row represented before and after your fix.

Work with your coding agent

AI Prompt

I am working through the audit lab in queries/audit-lab/. Do not tell me which queries are wrong.

Pick one query I have already formed a verdict on. Check my reasoning: is my verdict right, is the failure class I named the one that actually applies, and does my corrected query return the right number? If I am wrong, ask me a question that points at the fault rather than naming it.

Rubric

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

Area	Points	Evidence
Correct verdicts	3	All ten queries classified correctly, including the four that are right
Failure naming	2	Each wrong query is labelled with the failure class that actually applies
Corrected queries	2	Each fix runs and returns the right number
Both numbers stated	1	The wrong number and the right number are 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"

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. One related warning from the lab README applies to your agent too: 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 both.

Optional extensions

Finish the spine question. You now know everything the customer half needs: ask the agent which customers drove the growth in the winning categories, and audit the query - you know from q09 exactly what to check in a join to customers.
Ask the agent to audit the same ten queries, then compare its findings to yours. Where it disagreed with you, 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.

Answer key

Warning

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

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	Answer as written	Correct answer	Why
q01	correct	1,200	1,200	A plain `COUNT(*)` on one table. No join, no filter, nothing to fan out.
q02	wrong	London 261,246.42 (top row)	London 102,911.39	`order_total` is an order-header value, but the join to `order_items` repeats each order once per line. Sum it from `orders` alone.
q03	correct	202 orders per store, Paris 190	same	The join to `stores` only adds the city name. It does not change the grain, so `COUNT(*)` still counts orders.
q04	wrong	1,069	1,093	`!= 'cancelled'` silently drops the 24 NULL-status orders. Use `IS DISTINCT FROM 'cancelled'`.
q05	wrong	381,357.00	338,209.56	`unit_price` is the catalogue price. Revenue is what was charged: `quantity * net_price`.
q06	correct	503.39	503.39	`AVG(order_total)` over `orders` alone. One total per order, no join, right denominator.
q07	wrong	478	480	`ordered_at` is a timestamp, so `BETWEEN ... AND '2024-12-31'` stops at midnight and drops the two orders placed later that day. Use a half-open range.
q08	wrong	847,979.80 across 8 categories	851,617.69 including an Unknown bucket	15 order lines point at a `product_id` that is not in `products`. The INNER JOIN drops them and 3,637.89 with them.
q09	wrong	1,411.16	1,310.60	Ten duplicated rows in `customers` repeat those customers' orders in the join, inflating the numerator while `COUNT(DISTINCT customer_id)` stays correct - which is exactly why it looks safe.
q10	correct	2.4	2.4	Lines divided by distinct orders, both from `order_items` at its own grain.

One subtlety worth knowing if you checked q02 per store: the fan-out factor is exactly 2.4 across the whole table, but ranges from about 2.31 to 2.54 per store, because orders with more lines are not spread evenly. A per-store ratio near-but-not-equal to 2.4 is the data behaving correctly, not an error in your arithmetic.