SQL MOSAIC #1398. Customers Who Bought Products A and B but Not C

Date:7/6/2022

Write an SQL query to report the customer_id and customer_name of customers who bought products “A”, “B” but did not buy the product “C” since we want to recommend them to purchase this product.

Return the result table ordered by customer_id.

Thinking Pathway:

1. 3 conditions: customers who have bought product “A”, “B” but not “C”
2. filter for customers who have not bought “C”.
3. filter for customers who have bought “A” AND “B”

Solution(s):

This solution is straightforward.

SELECT o.customer_id, customer_name
FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id

WHERE o.customer_id NOT IN (
SELECT DISTINCT customer_id
FROM Orders
WHERE product_name = “C”)
AND o.customer_id IN (
SELECT DISTINCT customer_id
FROM Orders
WHERE product_name = “A”)
AND o.customer_id IN (
SELECT DISTINCT customer_id
FROM Orders
WHERE product_name = “B”)
GROUP BY o.customer_id