SQL Practice Online

-- Prompt: return the customer with the highest lifetime order total.
-- Schema: customers(customer_id, name), orders(order_id, customer_id, amount)

-- A common wrong answer that passes a single-fixture grader.
-- It happens to return 1 row on the visible test data, but the moment
-- 2 customers tie at the top, it returns nondeterministic results.
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY SUM(o.amount) DESC
LIMIT 1;

-- The replayed seeds engineer a tie. The query above returns "Alice"
-- on 1 seed and "Aliyah" on another. The error log:
--   seed_3: expected ["Alice","Aliyah"], got ["Aliyah"]
--   seed_7: expected ["Alice","Aliyah"], got ["Alice"]

-- The version that survives every seed handles the tie explicitly.
WITH totals AS (
  SELECT c.customer_id, c.name, SUM(o.amount) AS total
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.name
),
top_total AS (SELECT MAX(total) AS top FROM totals)
SELECT t.name
FROM totals t
JOIN top_total ON t.total = top_total.top
ORDER BY t.name;

The bug is silent on 1 fixture and obvious on 10. The replay-safe version takes 3 more lines and survives every seed.