이번에 시간이 조금 걸렸던 sql 코테에 대해서 다시 리뷰하려고 한다.
문제
Leetcode: Find Product Recommendation Pairs [medium]
고객들의 구매 데이터를 분석하여, 같은 고객이 함께 구매한 상품 쌍(product pair) 중공통 구매자가 2명 초과인 상품 조합을 찾아보세요.
각 상품 쌍에 대해 다음 정보를 출력해야 합니다:
product1_id, product2_id: 함께 구매된 상품 ID
product1_category, product2_category: 각각 상품의 카테고리
customer_count: 두 상품을 모두 구매한 고객 수
또한, customer_count가 2명 초과인 조합만 필터링하고,
customer_count 기준으로 내림차순 정렬하세요.출력 결과
product1_id | product2_id | product1_category | product2_category | customer_cnt
ProductPurchases 테이블
| 컬럼명 | 타입 | 설명 |
|---|---|---|
user_id | INT | 상품을 구매한 사용자 ID |
product_id | INT | 구매한 상품 ID |
quantity | INT | 구매 수량 |
ProductInfo 테이블
| 컬럼명 | 타입 | 설명 |
|---|---|---|
product_id | INT | 상품 ID (Primary Key) |
category | VARCHAR | 상품 카테고리 |
price | INT | 상품 가격 |
문제 접근 방식
처음에는 제품 쌍(product1, product2)별 product_id와 category 정보를 먼저 구성한 뒤,
이를 기반으로 JOIN을 이용해 함께 구매한 사용자 수를 구하고자 했습니다.
하지만 처음 구상한 방식대로는 잘 되지 않아 시행착오가 있었습니다.특히 CROSS JOIN은 평소에 자주 사용하지 않던 연산이라,
조합을 구성하는 부분에서 논리적으로 정리가 잘 되지 않았던 점이 아쉬웠습니다.최종적으로는 SELF JOIN과 CROSS JOIN을 활용하여 제품 간 조합을 구성하고,
중복 조합(예: 101–102 vs 102–101)을 제거하기 위해
조인 조건에 a.product_id < b.product_id를 추가하는 방식으로 문제를 해결했습니다.이 조건 덕분에 순서만 다른 동일한 조합이 중복으로 포함되는 것을 방지할 수 있었고, 불필요한 계산도 줄일 수 있었습니다.
정답 쿼리
WITH t as
(
SELECT user_id, pp.product_id, category
FROM productpurchases pp
JOIN productinfo pri on pp.product_id = pri.product_id
),
t2 as
(
SELECT a.product_id product1_id, b.product_id product2_id,
a.category product1_category, b.category product2_category
FROM t a
CROSS JOIN t b on a.product_id < b.product_id
GROUP BY 1, 2
),
t3 as
(
SELECT a.product_id product1_id, b.product_id product2_id,
count(distinct a.user_id) customer_count
FROM productpurchases a
JOIN productpurchases b on a.user_id = b.user_id and a.product_id < b.product_id
GROUP BY 1, 2
)
SELECT t2.product1_id, t2.product2_id,
product1_category, product2_category, customer_count
FROM t2
JOIN t3 on t2.product1_id = t3.product1_id and t2.product2_id = t3.product2_id
WHERE customer_count > 2
ORDER BY 5 desc, 1, 2;쿼리 최적화
사실 내가 처음 작성한 정답 쿼리는 구조가 깔끔하다고 보긴 어려웠고,
특히 t와 t2 테이블은 하나로 합쳐도 되겠다는 생각이 들었다.이런 생각을 바탕으로 ChatGPT를 활용해 쿼리를 다시 구성해 보았고,
불필요한 단계들을 줄이고 의도에 더 집중된 구조로 개선할 수 있었다.
# 기존 주문 테이블에 카테고리 추가
WITH purchases_with_cat AS (
SELECT pp.user_id, pp.product_id, pi.category
FROM productpurchases pp
JOIN productinfo pi ON pp.product_id = pi.product_id
),
# 앞에서 생성된 테이블을 바탕으로 self join 하여 제품 1, 2 별 주문 고객 수를 구함
product_pairs AS (
SELECT
a.product_id AS product1_id,
b.product_id AS product2_id,
a.category AS product1_category,
b.category AS product2_category,
COUNT(DISTINCT a.user_id) AS customer_count
FROM purchases_with_cat a
JOIN purchases_with_cat b
ON a.user_id = b.user_id
AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id, a.category, b.category
)
SELECT
product1_id,
product2_id,
product1_category,
product2_category,
customer_count
FROM product_pairs
WHERE customer_count > 2
ORDER BY customer_count DESC, product1_id, product2_id;가장 큰 차이점은 cross_join을 하지 않아서 메모리에 좋을 것 같다라는 생각을 했다.
