Include zero-order customers in order counts
Return every customer with their number of orders, including zero. Ignore orders whose customer_id does not match a customer. Return only the requested columns and follow the specified ordering. This question probes left join preserves all customers and count(o.id) excludes the synthetic null row.
Input
Setup script defining customers(id INT, name VARCHAR(30)); orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)).
Output
id, name, order_count; ascending customer ID.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Ana'),(2,'Ben'),(3,'Cara'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,100,'2024-01-01'),(2,1,200,'2024-02-01'),(3,2,100,'2025-01-01'),(4,NULL,50,'2024-12-31');
Expected result
id | name | order_count 1 | Ana | 2 2 | Ben | 1 3 | Cara | 0
Another dataset (1)
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Solo'),(2,'None'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,0,'2023-12-31'),(2,NULL,10,'2024-01-01');
Expected result
id | name | order_count 1 | Solo | 1 2 | None | 0
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Preserve customers with a LEFT JOIN.
- COUNT(*) would incorrectly return one for unmatched customers.
Target complexity: time O(n) · space O(n)