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

문제 링크

정답

-- USER_INFO: USER_ID, GENDER, AGE, JOINED
-- ONLINE_SALE: ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE
WITH 2021_USERS AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
),
TOTAL_2021_USERS AS (
    SELECT COUNT(*) AS TOTAL
    FROM 2021_USERS
)

SELECT 
    YEAR(O.SALES_DATE) AS YEAR, 
    MONTH(O.SALES_DATE) AS MONTH, 
    COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS,
    ROUND(
        COUNT(DISTINCT O.USER_ID) / 
        (SELECT TOTAL FROM TOTAL_2021_USERS),
        1
    ) AS PURCHASED_RATIO
FROM 2021_USERS AS U
JOIN ONLINE_SALE AS O
    ON U.USER_ID = O.USER_ID
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)
ORDER BY 1, 2;

✅ Point

• 1. 2021에 가입한 사람 CTE 선언
• 1. 2021에 가입한 사람 수 CTE 선언
• 1. 2021에 가입 & 구매 회원 JOIN
• 1. YEAR, MONTH 별로 GROUP
• 1. USER ID 별 중복 COUNT 를 방지하기 위해 DISTINCT COUNT