Customers Who Bought Products A and B but Not C

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

Database Medium

🛍️ Customer Product Recommendation Query

You're working for an e-commerce company that wants to implement a smart product recommendation system. Your task is to identify customers who have purchased both products "A" and "B" but have never bought product "C".

Given two tables:

Customers: Contains customer information (ID and name)
Orders: Contains order history (which customer bought which product)

Write a SQL query to find customers who:

✅ Have bought product "A"
✅ Have bought product "B"
❌ Have NOT bought product "C"

Return the customer_id and customer_name ordered by customer_id in ascending order.

Business Context: These customers are perfect targets for recommending product "C" since they've shown interest in related products but haven't made that purchase yet!

Input & Output

example_1.sql — Basic Case

$ Input: Customers: | customer_id | customer_name | |-------------|---------------| | 1 | Daniel | | 2 | Diana | | 3 | Elizabeth | | 4 | Jhon | Orders: | order_id | customer_id | product_name | |----------|-------------|---------------| | 10 | 1 | A | | 11 | 1 | B | | 12 | 1 | D | | 13 | 2 | A | | 14 | 2 | B | | 15 | 2 | C | | 16 | 3 | A | | 17 | 3 | C | | 18 | 4 | B | | 19 | 4 | C |

› Output: | customer_id | customer_name | |-------------|---------------| | 1 | Daniel |

💡 Note: Only Daniel (customer_id = 1) bought both products A and B but never bought product C. Diana bought A, B, and C (excluded). Elizabeth bought A and C but not B (excluded). Jhon bought B and C but not A (excluded).

example_2.sql — Multiple Valid Customers

$ Input: 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 | 2 | A | | 4 | 2 | B | | 5 | 3 | A | | 6 | 3 | C |

› Output: | customer_id | customer_name | |-------------|---------------| | 1 | Alice | | 2 | Bob |

💡 Note: Both Alice and Bob bought products A and B without ever buying C. Charlie bought A and C but not B, so he's excluded from the results.

example_3.sql — No Valid Customers

$ Input: Customers: | customer_id | customer_name | |-------------|---------------| | 1 | John | | 2 | Mary | Orders: | order_id | customer_id | product_name | |----------|-------------|---------------| | 1 | 1 | A | | 2 | 1 | C | | 3 | 2 | B | | 4 | 2 | C |

› Output: | customer_id | customer_name | |-------------|---------------|| (empty result)

💡 Note: No customers satisfy all conditions. John bought A and C but not B. Mary bought B and C but not A. Since both bought C, they're excluded from recommendations.

Visualization

Tap to expand

Understanding the Visualization

Collect Purchase History

Gather all customer purchase records from the orders database

Group by Customer

Organize all purchases by individual customer to see their complete buying pattern

Count Product Types

For each customer, count how many times they bought products A, B, and C

Apply Filter Rules

Select customers where A_count > 0, B_count > 0, and C_count = 0

Key Takeaway

🎯 Key Insight: Using GROUP BY with conditional aggregation (SUM + CASE) allows us to process all product conditions in one efficient database scan, making it perfect for large-scale recommendation systems.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through Orders table with grouping

✓ Linear Growth

Space Complexity

O(k)

Space for grouping by unique customers (k = number of customers)

✓ Linear Space

Constraints

1 ≤ customer_id ≤ 10⁵
1 ≤ order_id ≤ 10⁶
product_name can be any string, not limited to 'A', 'B', 'C'
A customer can have multiple orders for the same product
All customer_ids in Orders table exist in Customers table

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses GROUP BY with conditional aggregation to check all product conditions in a single table scan. By joining the tables and using SUM(CASE WHEN...) statements, we can efficiently count purchases of each product type and filter customers who bought A and B but not C, achieving O(n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n³)	O(1)	Use separate EXISTS/NOT EXISTS subqueries for each product condition
GROUP BY with Conditional Aggregation (Optimal)	O(n)	O(k)	Use GROUP BY with SUM and CASE to check all conditions in one table scan

Brute Force (Multiple Subqueries) — Algorithm Steps

Query customers table as the base
Add EXISTS subquery to check if customer bought product 'A'
Add EXISTS subquery to check if customer bought product 'B'
Add NOT EXISTS subquery to ensure customer never bought product 'C'
Order results by customer_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Check Product A

First subquery scans Orders table for product A purchases

Check Product B

Second subquery scans Orders table for product B purchases

Check Product C

Third subquery scans Orders table to exclude product C buyers

Combine Results

Database combines all conditions and returns matching customers

Code -

solution.c — C

// SQL solution using multiple subqueries
#include <stdio.h>

int main() {
    char* sqlQuery = 
    "SELECT c.customer_id, c.customer_name "
    "FROM Customers c "
    "WHERE EXISTS ("
    "    SELECT 1 FROM Orders o1 "
    "    WHERE o1.customer_id = c.customer_id "
    "    AND o1.product_name = 'A'"
    ") "
    "AND EXISTS ("
    "    SELECT 1 FROM Orders o2 "
    "    WHERE o2.customer_id = c.customer_id "
    "    AND o2.product_name = 'B'"
    ") "
    "AND NOT EXISTS ("
    "    SELECT 1 FROM Orders o3 "
    "    WHERE o3.customer_id = c.customer_id "
    "    AND o3.product_name = 'C'"
    ") "
    "ORDER BY c.customer_id;";
    
    printf("%s\n", sqlQuery);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n³)

Three separate subqueries each scanning the Orders table

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond query execution

✓ Linear Space

Constraints

1 ≤ customer_id ≤ 10⁵
1 ≤ order_id ≤ 10⁶
product_name can be any string, not limited to 'A', 'B', 'C'
A customer can have multiple orders for the same product
All customer_ids in Orders table exist in Customers table

42.0K Views

High Frequency

~15 min Avg. Time

1.5K 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

🛍️ Customer Product Recommendation Query

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler