[Programmers Lv.5] 상품을 구매한 회원 비율 구하기 - 131534

Question

 

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 

상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

 

Code (After 25.06.)

 

SELECT YEAR(SALES_DATE) AS YEAR
        , MONTH(SALES_DATE) AS MONTH
        , COUNT(DISTINCT S.USER_ID) AS PURCHASED_USERS
        , ROUND(COUNT(DISTINCT S.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021),1) AS PURCHASED_RATIO
        
FROM (SELECT * FROM USER_INFO WHERE YEAR(JOINED) = 2021) U
JOIN ONLINE_SALE S ON U.USER_ID = S.USER_ID

GROUP BY 1,2
ORDER BY 1,2

 

Code (Before)

 

WITH USER_2021 AS(
    SELECT *, COUNT(USER_ID) OVER() AS TOTAL
        FROM USER_INFO
        WHERE YEAR(JOINED) = 2021
)
SELECT
    YEAR(SALES_DATE) AS YEAR
    , MONTH(SALES_DATE) AS MONTH
    , COUNT(DISTINCT U.USER_ID) AS PURCHASED_USERS
    , ROUND(COUNT(DISTINCT U.USER_ID)/TOTAL, 1) AS PUCHASED_RATIO
    
    FROM USER_2021 U JOIN ONLINE_SALE S ON U.USER_ID = S.USER_ID
    
    GROUP BY 1, 2
    ORDER BY 1, 2

 

Code (Before)

 

SELECT YEAR(SALES_DATE) AS YEAR, 
        MONTH(SALES_DATE) AS MONTH,
        COUNT(DISTINCT U.USER_ID) AS PURCHASED_USERS,
        ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(DISTINCT USER_ID) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1) AS PUCHASED_RATIO
    FROM USER_INFO U JOIN ONLINE_SALE S 
      ON U.USER_ID = S.USER_ID
    WHERE YEAR(JOINED) = 2021
    GROUP BY 1, 2
    ORDER BY 1, 2

 

now me

On my github