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

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

-- 2021년에 가입한 회원 수
SELECT count(USER_ID) from USER_INFO 
where YEAR(JOINED) = 2021;

-- 2021년에 가입하고 구매한 회원
SELECT count(DISTINCT u.USER_ID) from USER_INFO u
join ONLINE_SALE o on u.USER_ID = o.USER_ID
where YEAR(u.JOINED) = 2021;

-- 회원의 비율
SELECT 
round(
(select count(DISTINCT u.USER_ID) from USER_INFO u
join ONLINE_SALE o on u.USER_ID = o.USER_ID
where YEAR(u.JOINED) = 2021) / count(u.USER_ID)
, 1)as PUCHASED_RATIO
from USER_INFO u
where YEAR(u.JOINED) = 2021;

-- 년, 월 별로 출력
SELECT 
year(o.SALES_DATE) as YEAR,
month(o.SALES_DATE) as MONTH,
count(DISTINCT u.USER_ID) as PURCHASED_USERS,
round(
count(DISTINCT u.USER_ID) / (SELECT count(USER_ID) 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
order by YEAR, MONTH;

코호트 분석 - https://www.codeit.kr/tutorials/183/cohort-analysis
Cohort : 특정 기간에 공통된 특성을 경험한 사용자 집단
Cohort Analysis : 시간의 흐름에 따라 해당 집단(Cohort)의 행동 변화를 추적하는 분석 기법

WITH 
-- 분석 대상 집단 정의
cohort AS (
    SELECT u.USER_ID
    FROM USER_INFO u
    WHERE u.JOINED >= '2021-01-01' AND u.JOINED < '2022-01-01' 
),
-- 코호트의 전체 인원 수
tot AS (
    SELECT COUNT(*) AS total_users FROM cohort
),
-- 코호트의 월별 구매 활동 집계
sales AS (
    SELECT
        YEAR(s.SALES_DATE) AS YEAR,
        MONTH(s.SALES_DATE) AS MONTH,
        COUNT(DISTINCT s.USER_ID) AS PURCHASED_USERS
    FROM ONLINE_SALE AS s
    INNER JOIN cohort AS c
    ON s.USER_ID = c.USER_ID
    GROUP BY YEAR, MONTH         
)
/* sales 실행 과정
1. ONLINE_SALE inner join cohort -- 합치기
2. ON s.USER_ID = c.USER_ID -- 동일한 user id만 뽑기
3. GROUP BY YEAR, MONTH -- 년, 월로 묶기
-- 계산
4. COUNT(DISTINCT s.USER_ID)
5. SELECT YEAR, MONTH, PURCHASED_USERS
*/

-- 결과 조합 및 비율 계산
SELECT 
    s.YEAR,
    s.MONTH,
    s.PURCHASED_USERS,
    ROUND (s.PURCHASED_USERS / t.total_users, 1) AS PUCHASED_RATIO
FROM sales AS s
CROSS JOIN tot AS t
ORDER BY s.YEAR, s.MONTH