Drill 6 of 12 in Interview Classics
0

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…