[LV.5] JOIN > 상품을 구매한 회원 비율 구하기

문제

https://school.programmers.co.kr/learn/courses/30/lessons/131534

프로그래머스

---

문제 설명

• USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중,
  상품을 구매한 회원 수와 상품을 구매한 회원의 비율을 '구매가 발생한' 연도와 월 기준으로 출력하는 SQL문을 작성
  • 구매한 회원 비율 = (2021년에 가입한 회원 중 상품을 구매한 회원수) / (2021년에 가입한 전체 회원 수)
  • 비율은 소수점 둘째 자리에서 반올림
• 출력 결과는 연도, 월 순으로 오름차순 정렬

---

최종 정답 코드

* CTE는 참고해야 될 테이블이 많을 때 (또는 복잡한 쿼리일 때) 사용하는 것을 추천

→ 단순한 쿼리에서는 오히려 복잡하게 보일 수 있고, 여러 개의 CTE를 중첩 사용하면 성능 이슈 발생 가능

SELECT DATE_FORMAT(O.SALES_DATE, '%Y') AS YEAR,
       DATE_FORMAT(O.SALES_DATE, '%m') AS MONTH,
       COUNT(DISTINCT U.USER_ID) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE joined LIKE '2021%'), 1) AS PURCHASED_RATIO
FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
WHERE U.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

---

정답 코드

WITH JOIN_2021 AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
),
TOTAL_USERS AS (
    SELECT COUNT(*) AS TOTAL_COUNT
    FROM JOIN_2021
)

SELECT 
    YEAR(O.SALES_DATE) AS YEAR,
    MONTH(O.SALES_DATE) AS MONTH,
    COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT O.USER_ID) / (SELECT TOTAL_COUNT FROM TOTAL_USERS), 1) AS PURCHASED_RATIO
FROM ONLINE_SALE O
JOIN JOIN_2021 J ON O.USER_ID = J.USER_ID
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)
ORDER BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)

---

코드 설명

1. JOIN_2021 CTE (공통 테이블 식)

WITH JOIN_2021 AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
),

 

• USER_INFO 테이블에서 JOINED 날짜의 연도가 2021년인 회원들만 추출
• 필요한 컬럼은 USER_ID 하나뿐이라 명시적으로 그것만 가져오기
• 👉 이 CTE는 2021년에 가입한 유저 목록을 정의

2. TOTAL_USERS CTE

TOTAL_USERS AS (
    SELECT COUNT(*) AS TOTAL_COUNT
    FROM JOIN_2021
)

 

• 위에서 만든 JOIN_2021 CTE를 사용해, 2021년에 가입한 전체 회원 수를 계산
• 이 값은 이후 비율 계산에 사용
• 👉 TOTAL_COUNT는 분모로 사용될 값

3. 메인 SELECT 쿼리

SELECT 
    YEAR(O.SALES_DATE) AS YEAR,
    MONTH(O.SALES_DATE) AS MONTH,
    COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT O.USER_ID) / (SELECT TOTAL_COUNT FROM TOTAL_USERS), 1) AS PURCHASED_RATIO
FROM ONLINE_SALE O
JOIN JOIN_2021 J ON O.USER_ID = J.USER_ID
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)
ORDER BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)

• FROM ONLINE_SALE O
  JOIN JOIN_2021 J ON O.USER_ID = J.USER_ID
  • ONLINE_SALE 테이블에서 2021년에 가입한 유저들의 구매 정보만 가져오기
  • 즉, 2021년 가입자 중 구매 이력이 있는 유저의 정보만 필터링

---

첫 번째 시도 (실패)

WITH JOIN_2021 AS (SELECT *
                  FROM USER_INFO
                  WHERE YEAR(JOINED) = 2021)

SELECT YEAR(O.SALES_DATE) AS YEAR,
       MONTH(O.SALES_DATE) AS MONTH,
       COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS,
       ROUND((COUNT(DISTINCT O.USER_ID) / COUNT(*)),2) AS PUCHASED_RATIO
FROM JOIN_2021 J JOIN ONLINE_SALE O ON J.USER_ID = O.USER_ID
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)
ORDER BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)

---

문제점

ROUND(COUNT(DISTINCT O.USER_ID) / COUNT(*), 2)

• COUNT(*)는 조인된 행의 수
  즉, 같은 유저가 여러 건 구매했을 경우 건수만큼 증가

---

두 번째 시도 (성공. 개선필요)

필터링 조건이 단순히 특정 조건을 만족하는 유저인지 확인하는 것뿐이라면, 굳이 JOIN을 써서 행을 늘릴 필요 없이 WHERE절에서 직접 필터링하는 방식이 훨씬 간단하고 효율적

WITH JOIN_2021 AS (SELECT *
                  FROM USER_INFO
                  WHERE YEAR(JOINED) = 2021),
     TOTAL_USERS AS (SELECT COUNT(DISTINCT USER_ID) AS TOTAL_COUNT
                    FROM JOIN_2021)

SELECT YEAR(SALES_DATE) AS YEAR,
       MONTH(SALES_DATE) AS MONTH,
       COUNT(DISTINCT USER_ID) AS PURCHASED_USERS,
       ROUND((COUNT(DISTINCT USER_ID) / (SELECT TOTAL_COUNT FROM TOTAL_USERS)),1) AS PUCHASED_RATIO
FROM ONLINE_SALE
WHERE USER_ID IN (SELECT USER_ID  
                 FROM JOIN_2021)
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR(SALES_DATE), MONTH(SALES_DATE)

---

개선점

1. IN (SELECT ...) → JOIN 으로 변경
- 지금은 성능상 문제는 없지만, IN 서브쿼리는 내부적으로 중첩 루프로 작동할 수 있어 대용량일 때 느려질 가능성 있음
- JOIN은 옵티마이저가 더 잘 최적화할 수 있고, 향후 추가 컬럼에도 유리

2. SELECT * 지양 → 명시적 컬럼 지정을 권장
- 가독성 + 불필요한 컬럼 제거 + 의도 명확

3. ALIAS 명확히 사용 (선택 사항)
- USER_ID, SALES_DATE 같은 컬럼들이 여러 테이블에 걸쳐 있을 수 있으므로, 명확히 O.USER_ID, O.SALES_DATE로 명시하면 혼란 방지