Amazon Easy Frequently Asked Classic

Customers
Who Ordered
Every Product

Find customers who have ordered all available products.

Table Schema

Inspect
Table

interactive

Column	Type	Description
customer_id	integer	Customer identifier.
product_id	integer	Ordered product identifier.

Column	Type	Description
product_id	integer	Product identifier.

Sample Data

Sample Input: products

Sample Input: orders

Expected Output

customer_id
101
103

SQL Editor

Waiting for query

customer_id
101
103

Hints

Hint 01: Count how many distinct products each customer ordered.

Hint 02: Compare that count to the total number of products.

Hint 03: Keep only customers whose product count matches the full catalog.

Solution

Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.

SELECT
  customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (
  SELECT COUNT(*)
  FROM products
)
ORDER BY customer_id;

Explanation

Count the distinct products ordered by each customer.

Compare that count against the total number of products.

Keep only customers whose distinct product count matches the product table.