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
- Count how many products the category has (here 2: Pen and Notebook).
- For each customer, count the different products from that category they have bought.
- Keep customers whose count equals the category's total: GROUP BY with HAVING
COUNT(DISTINCT …)= (subquery). - Or say it as a double negative: there is no Stationery product this customer has not bought.
Edge cases to mention
- An empty category: the counting version returns nobody, while the double-negative version returns every customer ("bought all of nothing"). Ask which is wanted.
- Buying the same product twice must not count twice: use
COUNT(DISTINCT). - Products added to the category later change the answer for past customers — that is usually intended.
Common mistakes
COUNT(*)instead ofCOUNT(DISTINCT product_id), so two notebooks look like two different products.- Hard-coding the total (= 2) instead of counting the category.
- Using IN ('Pen', 'Notebook'), which finds customers who bought any of them, not all.
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.