Intermediate Joins · LEFT JOIN · COUNT(col) vs COUNT(*)
17. Order count, including zero
Concept: LEFT JOIN · COUNT(col) vs COUNT(*)show
To count related rows and still show zeros, LEFT JOIN and count a column from the right table. COUNT(*) counts rows, and a LEFT JOIN gives a customer with no orders one row full of NULLs, so COUNT(*) says 1. COUNT(o.id) skips NULLs and says 0.
Example (different data):
SELECT a.name, COUNT(b.id) AS books FROM authors a LEFT JOIN books b ON b.author_id = a.id GROUP BY a.name;
Your task
Customer success wants every customer's order count, including the ones who have never ordered, so they can reach out to them.
Return name and order_count (number of orders of any status) for every customer, including customers with 0 orders. Sort by order_count (highest first), then name.
Solution unlocks after 3 more tries
Starting Postgres in your browser…
query.sql
Output
Write a query and press Run Query (Ctrl/⌘ + Enter).