Customers Who Bought All Products

Customers Who Bought All Products

What is this problem about?

The Customers Who Bought All Products interview question is a relational division problem. Given a Customer table (tracking which customers bought which product IDs) and a Product table (listing all available product IDs), you need to find the customers who have purchased every single product listed in the Product table. This Customers Who Bought All Products coding problem is about comparing a subset's size to the global set's size.

Why is this asked in interviews?

Companies like Apple and Bloomberg use this to test a candidate's ability to use subqueries and dynamic counting. Instead of hardcoding the number of products, your query must work regardless of how many products are in the database. It tests the concept of "Double Negation" (finding customers for whom there does not exist a product they haven't bought) or simple cardinality comparison.

Algorithmic pattern used

This utilizes the Database interview pattern of "Compare Aggregate to Subquery."

1. Count Total Products: Calculate the total number of distinct products in the Product table.
2. Count Customer Products: Group the Customer table by user and count their distinct product purchases.
3. Compare: Filter for customers whose count matches the total product count.

Example explanation

Products: {1, 2}. Customer A buys: {1, 2}. (Count 2) Customer B buys: {1, 1, 1}. (Count 1) Customer C buys: {2}. (Count 1) Only Customer A matches the total count of 2.

Common mistakes candidates make

• Forgetting DISTINCT: Counting the number of purchase records instead of unique product IDs. If a user buys product 1 three times, they haven't bought "all products" if product 2 also exists.
• Hardcoding the count: Writing HAVING COUNT(...) = 5 because the example had 5 products. This fails if the product list changes.
• Inefficient JOINs: Trying to join the product table for every purchase record when a simple count comparison is much faster.

Interview preparation tip

Relational division is a classic SQL concept. Practice the "count comparison" method as it is usually the most performant and easiest to explain. For very advanced roles, be prepared to discuss the NOT EXISTS double-nested subquery method as well.