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

https://school.programmers.co.kr/learn/courses/30/lessons/131534

• CTE 사용하기

• 서브쿼리 사용

-- 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원 수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문 작성

WITH TOTAL_2021_JOINED AS (
    SELECT
        USER_ID
    FROM
        USER_INFO
    WHERE 
        YEAR(JOINED) = 2021
),

BOUGHT_USER AS (
    SELECT
        T.USER_ID,
        O.SALES_DATE
    FROM
        TOTAL_2021_JOINED T
    JOIN 
        ONLINE_SALE O ON T.USER_ID = O.USER_ID
)


SELECT 
    YEAR(B.SALES_DATE) AS YEAR,
    MONTH(B.SALES_DATE) AS MONTH,
    COUNT(DISTINCT B.USER_ID) AS PURCHASED_USERS,
    ROUND(
        COUNT(DISTINCT B.USER_ID) / (SELECT COUNT(*) FROM TOTAL_2021_JOINED), 1
    ) AS PURCHASED_RATIO
FROM 
    TOTAL_2021_JOINED T
JOIN 
    BOUGHT_USER B ON T.USER_ID = B.USER_ID
GROUP BY
    YEAR,
    MONTH
ORDER BY
    YEAR, 
    MONTH

서브쿼리 없이 CTE만 사용

-- 1. 2021년에 가입한 회원 목록 CTE
WITH TOTAL_2021_JOINED AS (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
),

-- 2. 2021년 전체 가입자 '수'를 계산하는 CTE (결과는 단 1행)
TOTAL_COUNT AS (
    SELECT COUNT(USER_ID) AS CNT
    FROM TOTAL_2021_JOINED
),

-- 3. 2021년 가입자 중 월별 구매 회원 수를 집계하는 CTE
MONTHLY_PURCHASERS AS (
    SELECT
        YEAR(O.SALES_DATE) AS YR,
        MONTH(O.SALES_DATE) AS MTH,
        COUNT(DISTINCT T.USER_ID) AS PURCHASED_USERS
    FROM TOTAL_2021_JOINED T
    JOIN ONLINE_SALE O ON T.USER_ID = O.USER_ID
    GROUP BY
        YR, MTH
)

-- 최종 쿼리: 월별 구매 데이터와 전체 가입자 수를 CROSS JOIN 하여 비율 계산
SELECT
    MP.YR AS YEAR,
    MP.MTH AS MONTH,
    MP.PURCHASED_USERS,
    ROUND(MP.PURCHASED_USERS / TC.CNT, 1) AS PUCHASED_RATIO
FROM
    MONTHLY_PURCHASERS MP
CROSS JOIN
    TOTAL_COUNT TC -- 이 부분이 핵심입니다.
ORDER BY
    YEAR, MONTH;