Customers who bought every product in a category

Find the customers who have bought every product in the Stationery category.

What it tests

"Every" questions — relational division — which can't be answered with a plain join. They test counting distinct matches against a total, or thinking in double negatives.

The pattern

"All of" (relational division). "Every", "all of the", "each one of", "the complete set".

How to approach it

  1. Count how many products the category has (here 2: Pen and Notebook).
  2. For each customer, count the different products from that category they have bought.
  3. Keep customers whose count equals the category's total: GROUP BY with HAVING COUNT(DISTINCT …) = (subquery).
  4. Or say it as a double negative: there is no Stationery product this customer has not bought.

Edge cases to mention

Common mistakes

Not true: IN ('Pen', 'Notebook') finds the customers who bought both products.
In fact: IN is true when a row matches any value in the list, so it finds customers who bought at least one. To require all of them, count the different products each customer bought.

Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.