Skip to main content

[SQL] 131534 상품을 구매한 회원 비율 구하기

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

image.png

image.png

image.png

1. 문제

테이블

  • USER_INFO : 회원 정보 (USER_ID, GENDER, AGE, JOINED …)
  • ONLINE_SALE : 온라인 판매 내역 (ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE …)

조건

  • 2021년에 가입한 회원들만 대상으로 함 → `YEAR(JOINED) = 2021`
  • 이 회원들 중 실제로 상품을 구매한 회원 수 (`COUNT(DISTINCT USER_ID)`) 집계

출력

  • 년(`YEAR`), 월(`MONTH`)
  • 상품을 구매한 회원 수 (`PURCHASED_USERS`)
  • 상품을 구매한 회원 비율 (`PURCHASED_RATIO`)
  • 계산식: 구매한 회원 수 / 2021년에 가입한 전체 회원 수
  • ROUND(컬럼명, 1) → 소수점 첫째 자리까지 표시 (둘째 자리에서 반올림)

정렬

  • YEAR, MONTH 오름차순

2. 오답쿼리

-- 코드를 입력하세요
-- i.JOINED 가 2021 인 회원들 - CTE
-- o.테이블에 있는 COUNT(ONLINE_SALE_ID) / CTE
-- 비율 계산시 ROUND(컬럼, 1)까지
-- 정렬 YEAR, MONTH
WITH SIGN_IN AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = '2021'
)
SELECT YEAR(s.SALES_DATE), 
    MONTH(s.SALES_DATE), 
    COUNT(DISTINCT s.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT s.USER_ID) / COUNT(n.USER_ID), 1) AS PURCHASED_RATIO
FROM SIGN_IN n JOIN ONLINE_SALE s ON n.USER_ID = s.USER_ID
GROUP BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE)
ORDER BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE);

1. YEAR()함수 사용

WHERE YEAR(JOINED) = '2021'
WHERE YEAR(JOINED) = 2021

YEAR()함수 쓰면 결과값이 문자열이 아니라 숫자형으로 인식되므로 작은따옴표 없이 사용해야 한다.

2. PURCHASED_RATIO 구할 때 분모 범위 

ROUND(COUNT(DISTINCT s.USER_ID) / COUNT(n.USER_ID), 1) AS PURCHASED_RATIO
  • 처음에는 위와 같이 쿼리를 썼는데, COUNT(n.USER_ID)는 GROUP BY YEAR, MONTH 이후에 세어지는 값이라 사실상 '그 달에 구매한 회원수'랑 같은 범위에서 카운트된다.
  • 즉, 분자(COUNT(DISTINCT s.USER_ID))랑 분모(COUNT(n.USER_ID)) 가 똑같이 계산되니까 결과가 항상 1.0만 나와버린다.
  • 우리가 구하려는 건 그 달에 구매한 회원 수 / 2021년에 가입한 전체 회원수이다.
  • 그래서 분모를 COUNT(n.USER_ID) 같은 그룹 내 값이 아니라, 고정된 값인 SELECT COUNT(*) FROM SIGN_IN이 되어야 한다.
  • 분모에 다시 SELECT 쓰는거 가능한 이유는 스칼라 서브쿼리(scalar subquery)이기 때문이다. 결과가 단일 값(하나의 숫자)으로 떨어지면 SELECT절이나 WHERE절은 물론, 심지어 산술 연산에도 넣을 수 있다.

3. 정답쿼리

-- 코드를 입력하세요
-- i.JOINED 가 2021 인 회원들 - CTE
-- o.테이블에 있는 COUNT(ONLINE_SALE_ID) / CTE
-- 비율 계산시 ROUND(컬럼, 1)까지
-- 정렬 YEAR, MONTH
WITH SIGN_IN AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
)
SELECT YEAR(s.SALES_DATE), 
    MONTH(s.SALES_DATE), 
    COUNT(DISTINCT s.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT s.USER_ID) / (SELECT COUNT(*) FROM SIGN_IN), 1) AS PURCHASED_RATIO
FROM SIGN_IN n JOIN ONLINE_SALE s ON n.USER_ID = s.USER_ID
GROUP BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE)
ORDER BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE);

하지만 (SELECT COUNT(*) FROM SIGN_IN)은 서브쿼리라서 매번 실행될 수도 있다. 그럼 성능에 영향이 간다. 그래서 보통은 아예 CTE로 따로 빼거나, CROSS JOIN으로 한 번만 가져오도록 최적화하는게 좋다. 아래처럼 CTE 쓰면 분모 값을 딱 한 번만 계산하니까 더 깔끔하고 효율적이다.

WITH SIGN_IN AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
),
TOTAL_USERS AS (
    SELECT COUNT(*) AS CNT FROM SIGN_IN
)
SELECT 
    YEAR(s.SALES_DATE) AS YEAR,
    MONTH(s.SALES_DATE) AS MONTH,
    COUNT(DISTINCT s.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT s.USER_ID) / t.CNT, 1) AS PURCHASED_RATIO
FROM SIGN_IN n
JOIN ONLINE_SALE s ON n.USER_ID = s.USER_ID
CROSS JOIN TOTAL_USERS t
GROUP BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE)
ORDER BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE);


Back to top