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
Find Customers Who Bought All ProductsInput Tablescustomer_idproduct_key15161326product_key563GROUP BYCOUNTGrouped Analysiscustomer_idproduct_count1321Total Products: 3HAVINGCOUNT = 3Resultcustomer_id1Customer 1 bought all 3 products (5, 6, 3) ✓Customer 2 bought only 1 product (6) ✗
Understanding the Visualization
1
Input
Customer purchases and available products
2
Group & Count
Group by customer and count distinct products
3
Filter
Keep customers with complete product sets
Key Takeaway
🎯 Key Insight: Use GROUP BY with COUNT to identify complete sets in relational data

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