Customers Who Bought Products A and B but Not C - Solution & Explanation

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.