Interview Classics · Aggregate of an aggregate · LEFT JOIN · COUNT(column)
6. Customers by number of orders
Concept: Aggregate of an aggregate · LEFT JOIN · COUNT(column)show
Some questions need an aggregate of an aggregate: count per customer in a subquery, then GROUP BY that count to see how many customers have each count. To include customers with zero, start from customers and LEFT JOIN, and count a column of the joined table: COUNT(o.id) skips the NULL row a LEFT JOIN leaves, while COUNT(*) counts it as 1.
Example (different data):
SELECT posts, COUNT(*) AS authors FROM ( SELECT author_id, COUNT(*) AS posts FROM blog_posts GROUP BY author_id ) AS per_author GROUP BY posts ORDER BY posts;
Your task
Classic interview question: the CRM team wants a frequency histogram of how many customers have placed 0, 1, 2, ... completed orders.
Return completed_orders (a number of completed orders) and customers (how many customers have exactly that many), one row per count that occurs, including 0 for customers with no completed orders. Sort by completed_orders.
Starting Postgres in your browser…
Checking your access…