Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1045.html
1045. Customers Who Bought All Products
Level
Medium
Description
Table: Customer
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| customer_id | int |
| product_key | int |
+-------------+---------+
product_key is a foreign key to Product table.
Table: Product
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_key | int |
+-------------+---------+
product_key is the primary key column for this table.
Write an SQL query for a report that provides the customer ids from the Customer table that bought all the products in the Product table.
For example:
Customer table:
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1 | 5 |
| 2 | 6 |
| 3 | 5 |
| 3 | 6 |
| 1 | 6 |
+-------------+-------------+
Product table:
+-------------+
| product_key |
+-------------+
| 5 |
| 6 |
+-------------+
Result table:
+-------------+
| customer_id |
+-------------+
| 1 |
| 3 |
+-------------+
The customers who bought all the products (5 and 6) are customers with id 1 and 3.
Solution
First select the number of products each customer bought from table Customer. Next select the number of products in table Product. Finally join the two selection results and return the customers that have bought the same number of products as the total number of products.
# Write your MySQL query statement below
select distinct customer_id
from (
select customer_id, count(distinct product_key) as product_count
from Customer
group by customer_id
) as customer_product_counts
join (
select count(distinct product_key) as product_count from Product
) as product_counts
on customer_product_counts.product_count = product_counts.product_count;