[프로그래머스] Lv5. 상품을 구매한 회원 비율 구하기 / JOIN

💡문제

 

처음 제출한 코드

SELECT YEAR(O.sales_date), MONTH(O.sales_date), COUNT(U.user_id) AS PUCHASED_USERS
FROM user_info U JOIN online_sale O ON U.user_id = O.user_id
WHERE Year(U.joined) = 2021
GROUP BY YEAR(O.sales_date), MONTH(O.sales_date)

상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수) 테이블 필요

 

 

정답 코드

# 1 - WITH문
WITH rate AS (SELECT COUNT(DISTINCT user_id) rate_user -- 2021년에 가입한 회원수
                FROM user_info
                WHERE YEAR(joined)=2021)
                
SELECT YEAR(O.sales_date) YEAR, MONTH(O.sales_date) MONTH
    , COUNT(DISTINCT O.user_id) AS PUCHASED_USERS -- 상품구매한 회원수
    , ROUND(COUNT(DISTINCT O.user_id) / (SELECT rate_user -- rate 테이블 이용
                                        FROM rate), 1) AS PUCHASED_RATIO
FROM user_info U JOIN online_sale O ON U.user_id = O.user_id
WHERE YEAR(U.joined) = 2021
GROUP BY YEAR, MONTH -- 혹은 1,2
ORDER BY YEAR, MONTH

# 2 - WITH문 없이
SELECT YEAR(O.sales_date) YEAR, MONTH(O.sales_date) MONTH
    , COUNT(DISTINCT O.user_id) AS PUCHASED_USERS
    , ROUND(COUNT(DISTINCT O.user_id) / (SELECT COUNT(DISTINCT user_id) rate_user
                                        FROM user_info
                                        WHERE YEAR(joined)=2021), 1) AS PUCHASED_RATIO
FROM user_info U JOIN online_sale O ON U.user_id = O.user_id
WHERE YEAR(U.joined) = 2021
GROUP BY YEAR, MONTH -- 1,2
ORDER BY YEAR, MONTH

# 3
SELECT YEAR, MONTH
    , COUNT(*) AS PUCHASED_USERS
    , ROUND((COUNT(*)/ (SELECT COUNT(*) 
                        FROM USER_INFO 
                        WHERE YEAR(JOINED) = 2021)), 1) AS PUCHASED_RATIO
FROM (
    SELECT DISTINCT YEAR(O.sales_date) AS YEAR, MONTH(O.sales_date) AS MONTH, U.user_id
    FROM USER_INFO U JOIN ONLINE_SALE O
        ON U.USER_ID = O.USER_ID AND YEAR(joined) = 2021
    ) A
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH