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;

All Problems

All Solutions