$ ./guess_the_languageProblem Solving — fix the bug
Problem Solving — fix the bug · hard · #260

Which fix removes the bug?

SELECT u.name, COUNT(*) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
-- users with no orders show 1, not 0
  1. Switch to an INNER JOIN
  2. Use COUNT(DISTINCT u.id)
  3. Add HAVING COUNT(*) >= 0
  4. Count a right-side column: COUNT(o.id)
A user with no orders still gets one joined row (with NULLs), and COUNT(*) counts it. COUNT(o.id) skips the NULL and gives 0.
Play Problem Solving ▸