Customers Who Bought Products A and B but Not C - Problem

You have two tables: Customers and Orders.

The Customers table contains customer information with unique customer IDs and names. The Orders table records each product purchase by customers.

Write a SQL solution to find customers who have bought both products "A" and "B" but have never bought product "C". These customers are good candidates for product C recommendations.

Return the result table ordered by customer_id.

Table Schema

Customers
Column Name Type Description
customer_id PK int Unique customer identifier
customer_name varchar Name of the customer
Primary Key: customer_id
Orders
Column Name Type Description
order_id PK int Unique order identifier
customer_id int Foreign key referencing Customers table
product_name varchar Name of the product purchased
Primary Key: order_id

Input & Output

Example 1 — Basic Filtering
Input Tables:
Customers
customer_id customer_name
1 Daniel
2 Diana
3 Elizabeth
4 Jhon
Orders
order_id customer_id product_name
10 1 A
20 1 B
30 1 D
40 1 C
50 2 A
60 3 A
70 3 B
80 3 D
90 4 C
Output:
customer_id customer_name
3 Elizabeth
💡 Note:

Customer 1 (Daniel) bought A, B, and C, so excluded because they bought C. Customer 2 (Diana) only bought A, missing B. Customer 3 (Elizabeth) bought both A and B but not C, so included. Customer 4 (Jhon) only bought C, missing A and B.
Example 2 — Multiple Valid Customers
Input Tables:
Customers
customer_id customer_name
1 Alice
2 Bob
3 Charlie
Orders
order_id customer_id product_name
1 1 A
2 1 B
3 1 A
4 2 A
5 2 B
6 2 D
7 3 C
Output:
customer_id customer_name
1 Alice
2 Bob
💡 Note:

Alice bought products A and B (multiple times for A) but never bought C, so she's included. Bob bought A, B, and D but not C, so he's included. Charlie only bought C, missing both A and B, so excluded.
Example 3 — No Valid Customers
Input Tables:
Customers
customer_id customer_name
1 Mark
2 Sarah
Orders
order_id customer_id product_name
1 1 A
2 1 C
3 2 B
4 2 D
Output:
customer_id customer_name
💡 Note:

Mark bought A and C but not B (missing B). Sarah bought B and D but not A (missing A). Neither customer bought both A and B while avoiding C, so the result is empty.

Constraints

  • 1 ≤ customer_id ≤ 1000
  • 1 ≤ order_id ≤ 10000
  • product_name consists of only uppercase English letters
  • All customer names are unique

Visualization

Tap to expand
Customers Who Bought A and B but Not C INPUT DATA Customers Table id name 1 Alice 2 Bob 3 Charlie Orders Table cust_id product 1 A 1 B 2 A 2 B 2 C 3 A 3 B ALGORITHM STEPS 1 JOIN Tables Link Customers with Orders 2 GROUP BY customer Aggregate products per user 3 HAVING Condition Check A, B present; C absent 4 ORDER BY id Sort results by customer_id SELECT c.id, c.name FROM Customers c JOIN Orders o ON c.id=o.cust_id GROUP BY c.id HAVING SUM(A)>0 AND SUM(B)>0 AND SUM(C)=0 ORDER BY c.id FINAL RESULT Customer Analysis Alice (ID: 1) Bought: A, B | Not C OK - INCLUDED Bob (ID: 2) Bought: A, B, C EXCLUDED (has C) Charlie (ID: 3) Bought: A, B | Not C OK - INCLUDED Output Table id name 1 Alice 3 Charlie Key Insight: Use conditional aggregation with SUM(CASE WHEN product='X' THEN 1 ELSE 0 END) to count each product per customer. The HAVING clause then filters: customers with A count > 0, B count > 0, and C count = 0. This avoids complex subqueries and handles the "bought A AND B but NOT C" logic elegantly in one pass. TutorialsPoint - Customers Who Bought Products A and B but Not C | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
28.4K Views
High Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen