SQL Interview Questions – Customers Who Bought Products A and B but Not C

Problem Description –

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.

The query result format is in the following example.

Difficulty Level – Medium

Problem Link – Bought A AND B But Not C

Solution –

SELECT
    DISTINCT
    o.customer_id,
    c.customer_name
FROM Orders as o JOIN Customers as c
ON o.customer_id = c.customer_id
WHERE c.customer_id IN (SELECT customer_id FROM Orders WHERE product_name = 'A')
AND c.customer_id IN (SELECT customer_id FROM Orders WHERE product_name = 'B')
AND c.customer_id NOT IN (SELECT customer_id FROM Orders WHERE product_name = 'C')