The Report That Runs a Query per Customer

The support team at a Dublin online store has a customer report: every customer, with how many orders they've placed. It works, but it gets slower every month, and last week it started timing out.

Here's the query someone wrote a while ago:

SELECT c.id, c.name,
       (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c
ORDER BY c.id;

The subquery in the SELECT list is correlated: it mentions c.id, so Postgres runs it once for every customer. Each run scans the whole orders table, because nothing indexes customer_id. With 450 customers that's 450 full scans of 12,000 orders.

Your task: rewrite the query so Postgres reads orders once. Return the same columns (id, name, order_count) with the same values, one row per customer. A customer with no orders still appears, with order_count 0.

You can't add indexes in this lab. The fix is in how the query is written.

How this lab is graded. Your rows must match, as in any problem. On top of that, Postgres's estimated cost for your query must be at most 1.5× the model answer's on the same data. The cost is what plain EXPLAIN prints as the second number in cost=0.00..1234.56 on the top line. It's an estimate, not a stopwatch, so you get the same number every run. Run shows your cost, the target and the plan Postgres chose, so you can see which step reads the most.

Row order isn't graded. EXPLAIN the starter and look for SubPlan: that's the part that runs once per customer.