Customers who bought every product in a category

Step 1 Solve it yourself first

Tables you will use

customers orders order_items products

What it really 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".

Picture it

flowchart LR
  C["Stationery has 2 products<br/>Pen, Notebook"] --> Q{"customer bought<br/>2 different ones?"}
  Q -->|"Priya: Notebook and Pen"| K["kept"]
  Q -->|"everyone else: 0"| X["dropped"]

The approach, step by step

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.

SELECT c.name
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE p.category = 'Stationery'
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT p.product_id) = (
  SELECT COUNT(*) FROM products WHERE category = 'Stationery'
)
ORDER BY c.name;

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM products p
  WHERE p.category = 'Stationery'
    AND NOT EXISTS (
      SELECT 1
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.order_id
      WHERE o.customer_id = c.customer_id
        AND oi.product_id = p.product_id
    )
)
ORDER BY c.name;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Practice

  PostgreSQL Exercises — Aggregation

  pgexercises.com, free

• Practice

  DataLemur — relational division questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain the double NOT EXISTS version in plain words.

   Show answer

   It turns "bought every product" into a double negative: "there is no Stationery product that this customer has not bought".

   • The outer NOT EXISTS goes through each Stationery product, looking for one this customer is missing.
   • The inner NOT EXISTS answers, for one product: "has this customer never bought it?" (no item line of theirs has that product).
   • If the search finds no missing product, the customer has bought them all, and they are kept.

   It reads oddly, but it works without counting, and it handles products bought several times naturally.

2. Writefrom a blank page, then run it

   Find customers who bought every product in the Electronics category. (Who bought the phone case?)

   Show answer

   Change the category in both places. Nobody qualifies: Electronics has two products, the headphones and the phone case, and nobody has ever bought the phone case. Priya and Tom bought headphones, but one product out of two isn't "every product". An empty result is the right answer.

   sqlCopy

   SELECT c.name
   FROM customers c
   JOIN orders o       ON o.customer_id = c.customer_id
   JOIN order_items oi ON oi.order_id = o.order_id
   JOIN products p     ON p.product_id = oi.product_id
   WHERE p.category = 'Electronics'
   GROUP BY c.customer_id, c.name
   HAVING COUNT(DISTINCT p.product_id) = (
     SELECT COUNT(*) FROM products WHERE category = 'Electronics'
   )
   ORDER BY c.name;

   Try it

   No rows

   name

3. Predictsay it before you run it

   If Tom had also bought a pen and a notebook, what would the solution return?

   Show answer

   Priya and Tom. Stationery is the pen and the notebook. Priya already bought both; if Tom also bought one of each, he would have 2 distinct Stationery products too, matching the category's 2. Below, they are added to his order 104.

   sqlCopy

   INSERT INTO order_items VALUES (104, 1, 1), (104, 2, 1);
   SELECT c.name
   FROM customers c
   JOIN orders o       ON o.customer_id = c.customer_id
   JOIN order_items oi ON oi.order_id = o.order_id
   JOIN products p     ON p.product_id = oi.product_id
   WHERE p.category = 'Stationery'
   GROUP BY c.customer_id, c.name
   HAVING COUNT(DISTINCT p.product_id) = (
     SELECT COUNT(*) FROM products WHERE category = 'Stationery'
   )
   ORDER BY c.name;

   Try it

   2 rows

   name
   Priya
   Tom

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Show what each version returns for a category with no products, and explain why they differ.

   Show answer

   Take a category with no products, such as 'Toys'.

   • The COUNT version returns nobody. Its WHERE keeps only item lines in that category, and there are none, so there are no groups for HAVING to compare with 0.
   • The double NOT EXISTS version returns every customer (below). "Is there a Toys product this customer hasn't bought?" can never be yes when there are no Toys products, so every customer passes.

   Logically, the NOT EXISTS answer is the correct one: "bought every product in an empty set" is vacuously true, like "all my unicorns are pink". In practice, nobody wants that report, so the honest answer is to ask what the business wants for an empty category, and to handle it on purpose, for example by returning nothing when the category has no products.

   sqlCopy

   SELECT c.name
   FROM customers c
   WHERE NOT EXISTS (
     SELECT 1
     FROM products p
     WHERE p.category = 'Toys'
       AND NOT EXISTS (
         SELECT 1
         FROM orders o
         JOIN order_items oi ON oi.order_id = o.order_id
         WHERE o.customer_id = c.customer_id
           AND oi.product_id = p.product_id
       )
   )
   ORDER BY c.name;

   Try it

   6 rows

   name
   Lena
   Omar
   Priya
   Tom
   Tom
   Zoe

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

This is relational division — "bought all of". I join each customer to the category's products they bought, group by customer, and keep those whose count of distinct products equals the category's product count from a subquery. An equivalent formulation is a double NOT EXISTS: there's no product in the category that the customer hasn't bought. I'd mention the empty-category case, where the two versions disagree.

Follow-up questions

Tick each check when you can do it without notes