Customers Who Bought All Products
Amazon
Problem Statement
To improve marketing strategies, Amazon wants to identify "Completionist" customers—those who have bought at least one of every product currently listed in the catalog.
Write an SQL query to report the customer IDs from the Customer table that bought all the products in the Product table.
Rules:
- A customer must have bought every product_key present in the Product table.
- If a customer bought the same product multiple times, it should only be counted as one unique product purchase.
- Return the result table containing only the customer_id column.
- Results must be sorted by customer_id in ascending order.
Tests your understanding of
Basic SQL, Aggregation, HAVING Clause, Subquery and Distinct
Input Tables
customer
| customer_id(INTEGER) | product_key(INTEGER) |
|---|---|
| 1 | 5 |
| 2 | 6 |
| 3 | 5 |
| 3 | 6 |
| 1 | 6 |
product
| product_key(INTEGER) |
|---|
| 5 |
| 6 |
Expected Output
| customer_id(INTEGER) |
|---|
| 1 |
| 3 |
Tags
MediumBasic SQLAggregationHAVING ClauseSubqueryDistinct
10-15 min
60%