Customers Who Bought All Products | LeetCode 1045

This chapter covers LeetCode Problem 1045 ("Customers Who Bought All Products"), a medium-level SQL interview question that tests your mastery of aggregation, grouping, distinct counting, and subqueries in MySQL.

1. Problem Statement & Objectives

The core challenge in this problem is to identify and report every customer_id from the Customer table who has purchased all the products listed in the Product table. The result can be returned in any order.

Schema Overview

We are provided with two relational tables:

1. Customer Table:

   • Contains information about customer purchases.
   • May contain duplicate rows (a customer can buy the same product multiple times).
   • Columns: customer_id (INT), product_key (INT, foreign key referencing the Product table).

2. Product Table:

   • Contains the master list of all available products in the store.
   • Columns: product_key (INT, primary key).

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The Loyalty Club Completion Challenge

Imagine a coffee shop running a special loyalty campaign.

• The shop has a master menu (Product table) consisting of exactly 2 items: an Espresso (product_key = 5) and a Latte (product_key = 6).
• Customers walk in, order items, and their purchases are logged (Customer table).
• To win a grand prize, a customer must try every single unique item on the master menu at least once.
• To determine the winners, the store manager checks each customer's receipt history, counts how many distinct unique items they have purchased, and compares that count against the total count of items on the master menu ($2$). If a customer's unique purchase count equals the master menu count, they win!

3. Sample Data Walkthrough

Let's examine a concrete dataset to see how input rows map to expected outputs.

Input Data

Customer Table

Product Table

Step-by-Step Evaluation

1. Product Table Analysis: There are 2 unique products (5 and 6). Therefore, our target count is 2.
2. Customer 1: Bought products 5 and 6. Distinct count = 2. Matches target (2). $\rightarrow$ Include in output.
3. Customer 2: Bought product 6. Distinct count = 1. Does not match target (2). $\rightarrow$ Exclude.
4. Customer 3: Bought products 5 and 6. Distinct count = 2. Matches target (2). $\rightarrow$ Include in output.

Expected Output

4. Exploring Alternative Approaches

When solving this problem in an SQL interview, multiple strategies come to mind:

5. Step-by-Step SQL Query Construction

Let's build the optimal query step-by-step.

Step A: Selecting the Table and Grouping by Customer

We start by querying the Customer table and grouping rows by each individual customer so we can evaluate their purchases collectively.

SELECT customer_id
FROM Customer
GROUP BY customer_id;

Step B: Filtering with HAVING and COUNT(DISTINCT)

Since customers may buy the same product multiple times (resulting in duplicate rows in the Customer table), we must use COUNT(DISTINCT product_key) to count only unique products purchased by each customer.

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = ...;

Step C: Evaluating the Target Count via Subquery

What should the HAVING clause equate to? It must equal the total number of unique products present in the Product table. We dynamic compute this using a subquery:

(SELECT COUNT(product_key) FROM Product)

6. The Complete Production-Ready SQL Query

Putting all the pieces together, here is the clean, final query that passes all test cases on LeetCode:

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (
    SELECT COUNT(product_key) 
    FROM Product
);

7. Execution Flow & Debugging Mindset

When this query executes in a database engine like MySQL, the following sequence occurs:

Real-World Interview Edge Cases & Pitfalls

• Forgetting DISTINCT: If you write COUNT(product_key) instead of COUNT(DISTINCT product_key) on the Customer table, duplicate product purchases by the same customer will inflate their count, leading to incorrect filtering.
• Hardcoding Numbers: Never hardcode the expected product count (e.g., HAVING COUNT(DISTINCT product_key) = 2). Always use a subquery (SELECT COUNT(product_key) FROM Product) so the query remains robust if new products are added to the Product table later.
• Null Handling: Ensure foreign key constraints are respected so that orphaned product keys do not skew results.

8. Summary and Key Takeaways

• Problem Classification: Medium-level SQL query focusing on grouping, distinct aggregations, and subqueries.
• Master Table Reference: Always compute the denominator (total required items) dynamically using a subquery against the lookup table.
• Uniqueness Check: Use COUNT(DISTINCT column_name) when the transaction table allows duplicate entries for the same entity relationship.
• Filtering Groups: Use the HAVING clause rather than WHERE when filtering aggregated summary data (like counts).
• Extensibility: Dynamic subqueries ensure your solution is future-proof against schema updates and data growth.