A quarterly report written in 1998 and "temporarily" patched ever since. Its comment says it must list every active customer in the APAC group, including those with no 1999 orders. Finance says some never show up. Each model got the file, could not run it, and had to find the one broken line by reasoning and write the corrected query. A script then ran the corrected query against a test database with two customers who have no 1999 orders.
-- quarterly_report.sql (written 1998, "temporarily" patched ever since)
-- Intended result: EVERY active customer of the APAC group (including customers with no 1999 orders, shown with zero revenue).
-- One row per customer: name, region, order_count, net_revenue, paid, outstanding, balance_flag.
SELECT c.cust_name,
r.region_name,
IFNULL(o.order_count, 0) AS order_count,
IFNULL(o.net_revenue, 0) AS net_revenue,
IFNULL(p.paid, 0) AS paid,
IFNULL(o.net_revenue, 0) - IFNULL(p.paid, 0) AS outstanding,
CASE WHEN IFNULL(o.net_revenue, 0) - IFNULL(p.paid, 0) > 0 THEN 'OWES' ELSE 'SETTLED' END AS balance_flag
FROM customers c
JOIN regions r ON r.region_id = c.region_id
LEFT JOIN regions pr ON pr.region_id = r.parent_region_id
LEFT JOIN (SELECT od.cust_id,
COUNT(DISTINCT od.order_id) AS order_count,
SUM(ol.qty * ol.unit_price * (1 - ol.discount_pct/100.0)) AS net_revenue
FROM orders od
JOIN order_lines ol ON ol.order_id = od.order_id
WHERE od.order_date >= '1999-01-01' AND od.order_date < '2000-01-01'
AND od.status <> 'CANCELLED'
GROUP BY od.cust_id) o ON o.cust_id = c.cust_id
LEFT JOIN (SELECT od.cust_id, SUM(pm.amount) AS paid
FROM orders od
JOIN payments pm ON pm.order_id = od.order_id
WHERE od.order_date >= '1999-01-01' AND od.order_date < '2000-01-01'
GROUP BY od.cust_id) p ON p.cust_id = c.cust_id
LEFT JOIN credit_holds h ON h.cust_id = c.cust_id AND h.released_date IS NULL
WHERE c.active = 1
AND (r.region_name = 'APAC' OR pr.region_name = 'APAC')
AND h.cust_id IS NULL
AND o.order_count > 0
ORDER BY net_revenue DESC, c.cust_name;
The highlighted line is the answer key; the models never saw the highlight.
The bug is AND o.order_count > 0 in the WHERE clause, which is evaluated after the LEFT JOIN to the orders aggregate. For customers with no qualifying 1999 orders that join yields NULL for order_count and NULL > 0 is not true, so those rows are filtered out—effectively turning the LEFT JOIN into an inner join—despite the IFNULL(..., 0) expressions intended to display them with zero revenue.
14 s · $0.043 · 2039 tokens outfixed query checked against the test database
Judge: PASS (11 rows; missing: [] ; unexpected: [])
The bug is the predicate AND o.order_count > 0 in the WHERE clause.
Why it drops customers: A customer with no qualifying 1999 orders has no row in the o subquery, so the LEFT JOIN gives o.order_count = NULL, and NULL > 0 evaluates to UNKNOWN, which WHERE treats as false. That filter turns the LEFT JOIN into an inner join, so every zero-order customer disappears before the IFNULL(..., 0) in the SELECT list can show them with zero revenue.
The fix is to delete that one line; nothing else changes.
13 s · $0.033 · 1432 tokens outfixed query checked against the test database
Judge: PASS (11 rows; missing: [] ; unexpected: [])