Sweaty Imposter

Order Counts Including Zero

SQLJoinsJuniorMidUpdated
List every customer's email with their number of orders. Customers who have never ordered must appear with a count of 0.

Schema

CREATE TABLE customers (
  id     BIGINT PRIMARY KEY,
  email  TEXT NOT NULL
);

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(id),
  total        NUMERIC NOT NULL
);

Drill This: Your Query First

The Correct Query

Option (c)

SELECT c.email, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email;
The LEFT JOIN from customers keeps a customer with no orders as one row whose order columns are NULL. COUNT(o.id) skips NULLs, so that customer gets 0. COUNT(*) in (a) counts the row itself and reports 1 for someone who never ordered. INNER JOIN (b) drops customers without orders entirely. (d) puts orders on the left, so it keeps every order and loses the customers who have none.

Going Deeper

Joins multiply rows. If you join customers to orders and also to support tickets, a customer with 3 orders and 2 tickets becomes 6 rows, and both counts come out wrong. Count each child table in its own subquery (or use COUNT(DISTINCT o.id)) and join the results to customers. Grouping by c.id as well as c.email keeps two customers apart even if they share an email.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT c.email, COUNT(*) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email;

Option (b)

SELECT c.email, COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email;

Option (d)

SELECT c.email, COUNT(o.id) AS order_count
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
GROUP BY c.id, c.email;