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

프로그래머스

 

GROUP BY, DISTINCT를 잘 활용해야 하는 문제.

 

---

WITH REAL_USER_INFO AS (
    SELECT COUNT(*) AS CNT
    FROM USER_INFO
    WHERE JOINED LIKE "2021%"
),
REAL_ONLINE_SALE AS (
    SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, COUNT(DISTINCT USER_ID) AS CNT
    FROM ONLINE_SALE
    WHERE USER_ID IN
    (
        SELECT USER_ID
        FROM USER_INFO
        WHERE JOINED LIKE "2021%"
    )
    GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
)

SELECT O.YEAR, O.MONTH, O.CNT AS PURCHASED_USERS, ROUND((O.CNT / I.CNT), 1) AS PURCHASED_RATIO
FROM REAL_USER_INFO I, REAL_ONLINE_SALE O
ORDER BY 1, 2