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
Product Purchase Pattern FilteringOrders Tablecustomer_idproduct1A1B1C3A3B+ more orders...GROUP BYCOUNT A,B,CAggregated by Customercust_idA_cntB_cntC_cnt11113110HAVINGA>0,B>0,C=0Final Resultcustomer_idname3Elizabeth✓ Has A and B, No CCustomer 1: A=1, B=1, C=1 ✗(has C, excluded)Customer 3: A=1, B=1, C=0 ✓(perfect match!)Recommendationcandidates for Product C
Understanding the Visualization
1
Input Tables
Customers and their order history
2
Group & Count
Aggregate purchases by customer
3
Filter Results
Apply A+B+NotC condition
Key Takeaway
🎯 Key Insight: Use conditional aggregation with GROUP BY to efficiently count specific product purchases per customer, then filter with HAVING clause

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