SQL 코테 연습 프로그래머스 Lv.5 문제풀이 10

1. 상품을 구매한 회원 비율 구하기(JOIN) 정답률 : 45%

프로그래머스

문제

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

 

문제가 살짝 복잡해 보이는데, 우선 구해야하는것이 '2021년 가입 전체 회원수'와 '2021년 가입 회원 중 년월별 구매 회원수'이다. 우선 회원수 구하는데 있어 윈도우 함수를 사용하려한다. 전체 회원수를 구하는 테이블을 만들어서 구하고, 구매 년월별 회원수를 구하는 테이블을 추가로 구하여 두 테이블을 JOIN하였다. 

-- 1. COUNT() OVER(PARTITION BY ~) 사용
WITH TOTAL AS( -- 2021 가입 전체 회원수 구하는 테이블
SELECT USER_ID, COUNT(USER_ID) OVER() AS CNT_ALL
FROM USER_INFO U
WHERE YEAR(JOINED)='2021'
), SALE AS( -- 가입자 중 구매 연월별 회원수 구하는 테이블
SELECT *, COUNT(USER_ID) OVER(PARTITION BY YEAR, MONTH) AS CNT_PART
FROM (SELECT DISTINCT T.USER_ID, CNT_ALL, YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH 
        FROM TOTAL T
        INNER JOIN ONLINE_SALE O
            ON O.USER_ID=T.USER_ID) A
)SELECT DISTINCT YEAR, MONTH, CNT_PART AS PURCHASED_USERS, ROUND(CNT_PART/CNT_ALL,1) AS PUCHASED_RATIO
FROM SALE
ORDER BY YEAR, MONTH

 

그러나 너무 코드가 복잡하고, 굳이 WITH절 없이 구할 수 있을것 같아서 다시 코드를 짜보았다. 다시 짠 코드는 GROUP BY를 활용해서 건수를 구했고, JOIN을 하기 전에 전체 건수를 계산하고, 이후에 구해놓은 전체 건수를 비율 구하는데 활용했다. 

-- 2. 간단하게 WITH절 없이
SELECT YEAR, MONTH, COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS
        , ROUND(COUNT(DISTINCT O.USER_ID)/CUNT_TOT,1) AS PUCHASED_RATIO
FROM (SELECT *, COUNT(USER_ID) OVER() AS CUNT_TOT FROM USER_INFO 
        WHERE YEAR(JOINED)='2021') AS U
INNER JOIN (SELECT *, YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH
            FROM ONLINE_SALE) AS O
    ON O.USER_ID=U.USER_ID
GROUP BY 1,2
ORDER BY 1,2