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;