Customers Who Bought Products A and B but Not C - Solution & Explanation
Problem Statement
Table: Customers
+---------------------+---------+ | Column Name | Type | +---------------------+---------+ | customer_id | int | | customer_name | varchar | +---------------------+---------+ customer_id is the column with unique values for this table. customer_name is the name of the customer.
Table: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | customer_id | int | | product_name | varchar | +---------------+---------+ order_id is the column with unique values for this table. customer_id is the id of the customer who bought the product "product_name".
Write a solution 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 result format is in the following example.
Example 1:
Input: Customers table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | Daniel | | 2 | Diana | | 3 | Elizabeth | | 4 | Jhon | +-------------+---------------+ Orders table: +------------+--------------+---------------+ | 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 | +-------------+---------------+ Explanation: Only the customer_id with id 3 bought the product A and B but not the product C.
Approach Overview
Problem Overview: The task is to return customers who purchased product A and product B but never purchased product C. The data typically comes from a purchases or orders table where each row represents a customer-product transaction. The query must verify the presence of A and B while ensuring C does not exist for that same customer.
Approach 1: LEFT JOIN + GROUP BY + HAVING (O(n) time, O(n) space)
This approach groups all purchases by customer_id and evaluates which products each customer has bought. A LEFT JOIN can be used to isolate product C purchases while still keeping rows for A and B. After grouping, the HAVING clause checks three conditions: at least one purchase of product A, at least one purchase of product B, and zero purchases of product C. The key insight is that aggregation lets you compute conditional counts such as SUM(product = 'A') or COUNT(CASE WHEN product='C' THEN 1 END). This pattern is common in database interview problems where you must enforce multiple conditions across grouped records. Time complexity is O(n) because the table is scanned once for grouping, and space complexity is O(n) in the worst case due to grouping state.
Approach 2: Conditional Aggregation with GROUP BY (O(n) time, O(n) space)
Conditional aggregation is the most compact SQL pattern for this type of requirement. Instead of joining tables, you aggregate purchases per customer and count how many times each product appears using expressions like SUM(product = 'A'), SUM(product = 'B'), and SUM(product = 'C'). The HAVING clause then filters customers where A and B counts are greater than zero while C is exactly zero. This method relies heavily on SQL aggregation and filtering semantics and appears frequently in SQL interview questions. Execution typically involves a single table scan and grouping phase, giving O(n) time complexity and O(n) grouping space.
Approach 3: NOT EXISTS with Product Filters (O(n log n) typical, O(1) extra space)
Another way is to first identify customers who bought both A and B, then exclude those who purchased C using a NOT EXISTS subquery. The outer query selects customers with A and B transactions, while the subquery checks if any row exists for the same customer with product C. Databases can optimize EXISTS checks using indexes on (customer_id, product). This approach is common in relational query design and showcases mastery of joins and correlated subqueries.
Recommended for interviews: Conditional aggregation with GROUP BY and HAVING is usually the cleanest and most readable solution. It shows you understand SQL aggregation and filtering logic. Demonstrating the LEFT JOIN or NOT EXISTS variants signals deeper knowledge of relational query patterns and how different query plans achieve the same result.
Solution
We can use LEFT JOIN to join the Customers table and the Orders table, then group them by customer_id, and finally filter out the customers who have purchased products A and B but not product C.
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LEFT JOIN + GROUP BY + HAVING | O(n) | O(n) | When validating multiple product conditions with grouped customer data |
| Conditional Aggregation | O(n) | O(n) | Most concise SQL solution for checking presence and absence of values |
| NOT EXISTS Subquery | O(n log n) | O(1) | Useful when indexes support fast existence checks or when avoiding heavy aggregation |
Video Solution
LeetCode Medium 1398 Amazon Interview SQL Question with Detailed Explanation • Everyday Data Science • 5,030 views views
Frequently Asked Questions
Is Customers Who Bought Products A and B but Not C easy or hard?
Customers Who Bought Products A and B but Not C Python/Java solution
How to solve Customers Who Bought Products A and B but Not C in O(n)?
What is the best approach for Customers Who Bought Products A and B but Not C?
Is Customers Who Bought Products A and B but Not C asked at Google/Amazon/Meta?
What data structure is used in Customers Who Bought Products A and B but Not C?
What is the time complexity of Customers Who Bought Products A and B but Not C?
Ready to solve this problem?
Practice Customers Who Bought Products A and B but Not C with our built-in code editor and test cases.
Practice on FleetCode