105. Customers Who Bought All Products

[문제]

https://leetcode.com/problems/customers-who-bought-all-products/description/

 

[풀이]

1. customer 테이블에서 customer_id 별로 그룹화한 뒤 product_key를 세어준다.

2. 같은 상품을 여러 번 구매했을 수 있으므로 중복을 제거해준다.

3. product 테이블의 개수와 같은 customer_id 만 필터링한다.

 

Pandas

import pandas as pd

def find_customers(customer: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    df = customer.drop_duplicates().groupby('customer_id').count().reset_index()

    return df[df['product_key'] == len(product)][['customer_id']]

 

SQL

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