[1045] Customers Who Bought All Products

Table: Customer

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| customer_id | int     |
| product_key | int     |
+-------------+---------+
There is no primary key for this table. It may contain duplicates.
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 to report the customer ids from the Customer table that bought all the products in the Product table.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Customer table:
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+
Product table:
+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+
Output: 
+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
Explanation: 
The customers who bought all the products (5 and 6) are customers with IDs 1 and 3.

 

문제 조건 

Product 테이블의 모든 제품을 구매한 Customer 테이블의 고객 ID를 출력하시오 

 

풀이 과정

모든 제품을 구매 한 customer을 찾아야 하기 때문에 group by를 해서 묶어줌 

distinct product_key를 count 한 값이, product 테이블의 product_key를 count한 값과 같다면 

그 사람은 모든 제품을 구매 한 적이 있는 것이 되는 것.

 

처음에는 where을 쓸 수 있을까 했는데 그렇게 되면 id별 묶어지지 않으니까... 

SELECT customer_id
FROM Customer 
GROUP BY customer_id 
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(product_key)
                                     FROM Product)