SQL 고득점 Kit - 8.16

김동현·2024년 8월 16일

SQL 고득점 Kit

목록 보기

30/56

JOIN - 상품을 구매한 회원 비율 구하기

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

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

1차 풀이

WITH users AS ( -- 2021에 가입한 회원 추출
    SELECT *	
    FROM USER_INFO 
    WHERE JOINED LIKE '2021%'
)

SELECT 
    YEAR(os.sales_date) AS YEAR, -- 구매한 년도
    MONTH(os.sales_date) AS MONTH, -- 구매한 월
    count(DISTINCT os.USER_ID) AS PURCHASED_USERS, -- 월별 상품을 구매한 회원 수 (중복X) 월별
    --  2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수
    ROUND((count(DISTINCT os.USER_ID) / count(u.USER_ID)), 1) AS PUCHASED_RATIO 
FROM ONLINE_SALE os
INNER JOIN users u
ON 
    os.USER_ID = u.USER_ID
GROUP BY 
    MONTH(os.sales_date)
ORDER BY 
    YEAR(os.sales_date), MONTH(os.sales_date)

리뷰

문제 접근 방식을 먼저 이야기 해보자
1. 2021년 가입 인원 추출 쿼리 작성
2. 두 테이블 JOIN으로 판매한 테이블의 월별 그룹화로 최종 출력문을 위한 조건 만족
3. COUNT를 이용하여 회원 수 및 회원의 비율을 계산 후 소수점 2번째에서 반올림
4. 정렬 진행

조건을 만족하는 쿼리에 주석으로 설명을 달아서 실수한 부분을 찾아보았다.
문제를 풀기 위한 생각을 하고 진행했을 때 큰 문제가 없다고 생각했는데 정답이 아니라서 다시 한 번 문제 조건과 쿼리 부분을 검토한 결과 2021년에 가입한 전체 회원 수 인데 내가 구한 것을 JOIN을 통한 월별 회원 수를 구한 것이다.

2차 풀이

WITH users AS ( -- 2021에 가입한 회원 추출
    SELECT 
        *,
        (SELECT COUNT(*)
         FROM USER_INFO 
        WHERE JOINED LIKE '2021%') as cnt	-- 2021년에 가입한 전체 회원 수 카운트
    FROM USER_INFO 
    WHERE JOINED LIKE '2021%'
)

SELECT 
    YEAR(os.sales_date) AS YEAR, -- 구매한 년도
    MONTH(os.sales_date) AS MONTH, -- 구매한 월
    count(DISTINCT os.USER_ID) AS PURCHASED_USERS, -- 월별 상품을 구매한 회원 수 (중복X) 월별
    --  2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수
    ROUND((count(DISTINCT os.USER_ID) / u.cnt), 1) AS PUCHASED_RATIO 
FROM ONLINE_SALE os
INNER JOIN users u
ON 
    os.USER_ID = u.USER_ID
GROUP BY 
    MONTH(os.sales_date)
ORDER BY 
    YEAR(os.sales_date), MONTH(os.sales_date)

리뷰

처음에 u.USER_ID 부분에 서브 쿼리로 전체 카운트를 진행하려고 (SELECT COUNT(*) FROM users)을 통해 해결하려고 했지만 쿼리가 너무 복잡해보였다... ~~CTE에서 작성한 것도 복잡함.~~
그래서 CTE에서 다시 해당 부분을 카운트 할 수 있게 하는데 users로 바로 카운트를 하려고 했지만 CTE안에서 자기 자신의 테이블을 불러오는 것이 불가능한 것 같다. 이유는 아직 할당?되지 않은 쿼리를 불러오라고 하니 존재하지 않는 쿼리 테이블을 어디서 찾을까 ㅎㅎ...
좀 더 가독성 좋게 쿼리를 수정하고 싶다.