SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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).