Customers Who Bought Products A and B but Not C

Customers Who Bought Products A and B but Not C

What is this problem about?

The Customers Who Bought Products A and B but Not C interview question is a precise filtering task in SQL. You are typically given a table of orders or customers and products. The objective is to identify customers who have an "inclusive" history with specific products (A and B) while having an "exclusive" relationship with another (C). This Customers Who Bought Products A and B but Not C coding problem tests your ability to handle multiple conditions that must be true across different rows for the same user.

Why is this asked in interviews?

Meta and other social media or e-commerce companies ask this to evaluate how you handle relational sets. It’s not a simple WHERE product IN (A, B) because that would return anyone who bought either. This requires ensuring both A and B exist for the user and then verifying the absence of C. It tests proficiency with subqueries, self-joins, or advanced aggregation with HAVING.

Algorithmic pattern used

This follows the Database interview pattern of "Set Intersection and Difference."

1. Intersection: Find the set of customers who bought Product A AND the set of customers who bought Product B.
2. Difference: Subtract the set of customers who bought Product C from the resulting intersection.
3. Alternative: Group by customer and use SUM(CASE WHEN ...) to count occurrences of A, B, and C, then filter for count_A > 0 AND count_B > 0 AND count_C = 0.

Example explanation

Imagine three customers:

• Customer 1: Bought {A, B}.
• Customer 2: Bought {A, B, C}.
• Customer 3: Bought {A}. The query first checks who bought A and B. That's Customers 1 and 2. Then it checks who didn't buy C. Customer 1 remains. Result: Customer 1.

Common mistakes candidates make

• Simple OR Logic: Using WHERE product = 'A' AND product = 'B', which returns nothing because a single row cannot have two different product values.
• Incorrect Exclusion: Using WHERE product != 'C', which only removes the row for Product C but doesn't remove the customer if they have other product rows.
• Null Handling: Forgetting that if a customer has no orders, they might be missed or incorrectly included depending on the join type.

Interview preparation tip

Whenever you need to check for the presence of some rows and the absence of others for the same ID, the "Aggregation with Boolean Flags" (using MAX(CASE...)) is usually the most readable and efficient approach in SQL.