Reliable Data Engineering
Practice problem easy aggregationleft-joinhistogram
Solve it in the browser (SQL editor)

Histogram of Orders per Customer

Difficulty: Easy · Topics: aggregation, left-join, histogram · Asked at: Meta, Twitter/X, Etsy

Problem

Build a histogram: for each num_orders value (0, 1, 2, …), how many customers placed exactly that many orders in 2026? Include customers with zero orders. Order by num_orders.

Schema and sample data

CREATE TABLE customers (id INTEGER PRIMARY KEY, signup_date TEXT);
CREATE TABLE orders (id INTEGER, customer_id INTEGER, order_date TEXT);
INSERT INTO customers VALUES (1,'2025-12-01'),(2,'2026-01-03'),(3,'2026-01-09'),(4,'2026-02-11'),(5,'2026-02-20');
INSERT INTO orders VALUES (1,1,'2026-01-02'),(2,1,'2026-01-09'),(3,2,'2026-01-05'),(4,3,'2025-12-31'),
(5,1,'2026-02-01'),(6,4,'2026-02-12'),(7,2,'2026-03-01');

Expected output

num_orderscustomers
02
11
21
31

Hints

Hint 1

First count orders per customer (LEFT JOIN so zero-order customers survive), then count customers per value.

Hint 2

Put the 2026 filter in the ON clause, not WHERE. Why?

Solution

WITH per_customer AS (
  SELECT c.id, COUNT(o.id) AS num_orders
  FROM customers c
  LEFT JOIN orders o
    ON o.customer_id = c.id AND o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01'
  GROUP BY c.id
)
SELECT num_orders, COUNT(*) AS customers
FROM per_customer
GROUP BY num_orders
ORDER BY num_orders;

Explanation