Customers Who Bought All Products - Problem

You have two tables: Customer and Product.

The Customer table contains information about customers and the products they bought. This table may have duplicate rows, and customer_id is never NULL. The product_key is a foreign key referencing the Product table.

The Product table contains all available products, where product_key is the primary key.

Write a SQL query to find all customers who bought ALL products available in the Product table.

Return the result in any order.

Table Schema

Customer
Column Name Type Description
customer_id int ID of the customer (not NULL)
product_key int Foreign key referencing Product table
Note: May contain duplicate rows
Product
Column Name Type Description
product_key PK int Primary key for products
Primary Key: product_key
Note: Contains all available products

Input & Output

Example 1 — Complete Purchase Coverage
Input Tables:
Customer
customer_id product_key
1 5
2 6
1 6
1 3
Product
product_key
5
6
3
Output:
customer_id
1
💡 Note:

Customer 1 bought products 5, 6, and 3 (all available products). Customer 2 only bought product 6. Therefore, only customer 1 qualifies.
Example 2 — No Complete Customers
Input Tables:
Customer
customer_id product_key
1 5
2 6
3 5
Product
product_key
5
6
3
Output:
customer_id
💡 Note:

No customer bought all three products (5, 6, and 3). Each customer only purchased one product, so the result is empty.
Example 3 — Multiple Complete Customers
Input Tables:
Customer
customer_id product_key
1 5
1 6
2 5
2 6
Product
product_key
5
6
Output:
customer_id
1
2
💡 Note:

Both customers 1 and 2 purchased all available products (5 and 6), so both are included in the result.

Constraints

  • customer_id and product_key are integers
  • customer_id is not NULL
  • product_key is a foreign key to Product table
  • Customer table may contain duplicate rows

Visualization

Tap to expand
Customers Who Bought All Products INPUT Customer Table cust_id prod 1 5 1 6 2 5 2 6 2 7 3 5 Product Table product_id 5 6 7 Total Products: 3 (IDs: 5, 6, 7) Cust 1 [5, 6] Cust 2 [5, 6, 7] Cust 3 [5] Distinct products per customer ALGORITHM STEPS 1 Count Total Products SELECT COUNT(*) FROM Product Result: 3 2 Group by Customer GROUP BY customer_id Remove duplicate purchases 3 Count DISTINCT Products COUNT(DISTINCT product_key) Per customer unique count 4 HAVING Clause Filter HAVING COUNT = Total Keep only if all bought Cust1: 2 products (2 != 3) SKIP Cust2: 3 products (3 == 3) OK Cust3: 1 product (1 != 3) SKIP FINAL RESULT Customers with ALL Products Cust 1 2/3 Cust 2 3/3 Cust 3 1/3 Output customer_id: 2 Customer 2 bought ALL 3 products: 5, 6, 7 SELECT customer_id FROM Customer GROUP BY customer_id HAVING COUNT(DISTINCT prod) = (SELECT COUNT(*) FROM Product) Key Insight: Use HAVING with COUNT(DISTINCT) to compare each customer's unique purchases against total products. The subquery counts all products, and only customers whose distinct purchase count matches are returned. Time Complexity: O(n) where n = rows in Customer table | Space Complexity: O(m) for grouping m customers TutorialsPoint - Customers Who Bought All Products | Optimal Solution

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
31.5K Views
Medium Frequency
~12 min Avg. Time
892 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