Customers Who Bought All Products - Practice Coding Problems

Customers Who Bought All Products - Problem

Database Medium

Find the Complete Customers

You're managing an e-commerce database with two key tables: Customer and Product. Your goal is to identify the loyal customers who have purchased every single product available in your catalog.

The Customer table records each purchase transaction with columns customer_id and product_key (may contain duplicates if a customer bought the same product multiple times).

The Product table contains all available products with their unique product_key.

Your task: Write a SQL query to return the customer_id of customers who have bought all products from the Product table. Think of it as finding your "premium" customers who've experienced your entire product range!

Input & Output

example_1.sql — Basic Case

$ Input: Customer table:\n| customer_id | product_key |\n|-------------|-------------|\n| 1 | 5 |\n| 2 | 6 |\n| 3 | 5 |\n| 3 | 6 |\n| 1 | 6 |\n\nProduct table:\n| product_key |\n|-------------|\n| 5 |\n| 6 |

› Output: | customer_id |\n|-------------|\n| 1 |\n| 3 |

💡 Note: Customer 1 bought products 5 and 6 (all products). Customer 3 also bought products 5 and 6 (all products). Customer 2 only bought product 6, missing product 5.

example_2.sql — Single Product

$ Input: Customer table:\n| customer_id | product_key |\n|-------------|-------------|\n| 1 | 5 |\n| 2 | 5 |\n| 3 | 5 |\n\nProduct table:\n| product_key |\n|-------------|\n| 5 |

› Output: | customer_id |\n|-------------|\n| 1 |\n| 2 |\n| 3 |

💡 Note: There's only one product (5) in the catalog, and all customers have purchased it, so all customers qualify.

example_3.sql — No Complete Customers

$ Input: Customer table:\n| customer_id | product_key |\n|-------------|-------------|\n| 1 | 5 |\n| 2 | 6 |\n| 3 | 7 |\n\nProduct table:\n| product_key |\n|-------------|\n| 5 |\n| 6 |\n| 7 |

› Output: | customer_id |\n|-------------|\n(empty result)

💡 Note: Each customer only bought one product, but there are three products total. No customer bought all products, so the result is empty.

Constraints

1 ≤ customer_id ≤ 100
1 ≤ product_key ≤ 100
Customer table may contain duplicate rows
All product_key values in Customer table exist in Product table
Product table contains unique product_key values

Visualization

Tap to expand

Understanding the Visualization

Count Total Cards

First, determine how many cards are in the complete set

Group Collections

Group each customer's card purchases together

Count Unique Cards

For each customer, count how many different cards they own

Find Complete Sets

Identify customers whose unique card count equals the complete set size

Key Takeaway

🎯 Key Insight: Instead of checking each product individually, we count distinct products per customer and compare with the total - much more efficient!

Asked in

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

The optimal solution uses GROUP BY with COUNT(DISTINCT product_key) to count unique products per customer, then filters customers whose count equals the total product count using HAVING clause. This approach is efficient with O(n) time complexity and leverages SQL aggregation optimization.

Common Approaches

Approach	Time	Space	Notes
✓ Subquery for Each Customer	O(n²)	O(1)	Use correlated subqueries to check if each customer bought all products
GROUP BY with COUNT (Optimal)	O(n)	O(k)	Count distinct products per customer and compare with total product count

Subquery for Each Customer — Algorithm Steps

Select distinct customer_id from Customer table
For each customer, use a correlated subquery to check if they bought all products
Use NOT EXISTS to ensure no product is missing from customer's purchases

Visualization

Tap to expand

Step-by-Step Walkthrough

Select Customer

Pick a customer from the Customer table

Check Products

For this customer, verify they bought each product from Product table

Nested Loop

Repeat for every customer (inefficient!)

Code -

solution.c — C

// SQL Query using correlated subquery
char query[] = "\
SELECT DISTINCT c1.customer_id \
FROM Customer c1 \
WHERE NOT EXISTS ( \
    SELECT p.product_key \
    FROM Product p \
    WHERE p.product_key NOT IN ( \
        SELECT c2.product_key \
        FROM Customer c2 \
        WHERE c2.customer_id = c1.customer_id \
    ) \
)\
";

// Alternative approach
char queryAlt[] = "\
SELECT DISTINCT customer_id \
FROM Customer c1 \
WHERE ( \
    SELECT COUNT(DISTINCT product_key) \
    FROM Product \
) = ( \
    SELECT COUNT(DISTINCT product_key) \
    FROM Customer c2 \
    WHERE c2.customer_id = c1.customer_id \
)\
";

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each customer, we check against all products in a nested query

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond query execution

✓ Linear Space

48.3K Views

High Frequency

~15 min Avg. Time

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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Subquery for Each Customer — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler