Customers Who Bought All Products

Find customers who have purchased every product in the catalogue.

Problem statement

Prompt

Given the customer and product tables below, write a query that returns the customer_id of every customer who has bought every product in the product table.

Output a single column: customer_id, ordered by customer_id.
Buying a product more than once does not help — each distinct product must appear.

Example

customer_id	product_key
1	5
1	6
2	5
2	6

product_key
5
6

Expected result:

customer_id
1
2

Constraints

customer has no primary key; the same (customer, product) pair can repeat.
product.product_key is the primary key.

Tips

Group by customer and require COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM product).
This is the classic relational division problem.

Schema

CREATE TABLE customer (
    customer_id INTEGER NOT NULL,
    product_key INTEGER NOT NULL
);

CREATE TABLE product (
    product_key INTEGER PRIMARY KEY
);

Sample data

INSERT INTO product (product_key) VALUES (5), (6);

INSERT INTO customer (customer_id, product_key) VALUES
  (1, 5),
  (1, 6),
  (2, 5),
  (2, 5),   -- repeated purchase: still only product 5
  (3, 6);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: customers whose distinct purchases cover every product.
SELECT customer_id
FROM customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM product)
ORDER BY customer_id;

Key concepts

HAVING COUNT(DISTINCT)
Subquery
Relational division