SQL 코드카타_08

SQL 코드카타_Find Category Recommendation Pairs

Find Category Recommendation Pairs

문제 : Find Category Recommendation Pairs

# 목적, 목표 : 모든 카테고리 쌍 중에 최소한 서로 다른 고객이 두 카테고리 모두에서 제품을 구매한 경우에 카테고리 쌍과 고유 구매고객 수 추출
# 필요한 컬럼 : category1, category2, customer_count
# 컬럼 조건 : category1 <> category2, customer_count = count(distinct user_id)
# 사용할 테이블 : ProductPurchases, ProductInfo
# join key : product_id
# 테이블 조건 : customer_count > 2
# 필요한 그룹 : category1, category2
# 필요한 그룹 조건 : 없음
# 정렬 기준 : customer_count desc, category1, category2
# 주의사항 : 한명의 고유한 고객이 두 카테고리를 모두 구매한 경우에만 count에 포함되어야 함

로직

• ProductPurchases, ProductInfo 테이블 결합 (INNER JOIN)
• 카테고리 쌍 생성
  • 자기 자신 CROSS JOIN으로 가능한 모든 카테고리 쌍 생성
  • 단, (A, A) 제외 AND (A, B) = (B, A) 이므로 제외
  • 각 카테고리별로 구매한 user_id 추출
    • customer_count 계산을 위해
• customer_count 계산
  • category1, category2 기준 그룹화
  • category1을 구매한 고객 = category2를 구매한 고객만 count
• 조건에 맞춰 최종 정리
  • customer_count > 2만 추출
  • customer_count desc, category1, category2 정렬

코드 뜯어보기

# 테이블 결합
with A as (
    select p.user_id, p.product_id, i.category, p.quantity, i.price
    from ProductPurchases p inner join ProductInfo i on p.product_id = i.product_id
),

# 카테고리 쌍 생성
B as (
    select distinct least(aa.category, a.category) as category1, -- 둘 중 작은 값만 반환
            greatest(aa.category, a.category) as category2, -- 둘 중 큰 값만 반환
            a.user_id as auser, aa.user_id as aauser  -- 각 카테고리별 구매 고객 id 별도 추출
    from A a cross join A aa
    where least(aa.category, a.category) <> greatest(aa.category, a.category)
),

# customer_count 계산
C as (
    select category1, category2, count(distinct auser) as customer_count
    from B
    where auser = aauser  -- 두 카테고리 모두 구매한 고객만 필터링
    group by category1, category2
    order by count(distinct auser) desc, category1, category2
)

# 조건에 맞춰 최종 정리
select *
from C
where customer_count > 2
order by customer_count desc, category1, category2

새로 알게된 점

• (A, B) = (B, A)이므로 제외하기 위해 아래 방식 필요함
  • least(A, B) : 두 값 중 작은 값을 반환
  • greatest(A, B) : 두 값 중 큰 값을 반환
  • distinct로 중복 제거하면, (B, A) 제거됨