SQL 8일차

🟦 문제 D: 구독 전환율 분석 ★★★★★★ (3번 틀림)

✅ 테이블

subscriptions(user_id, event_type, event_time)

• event_type은 'free_trial_start', 'paid_subscribe' 둘 중 하나

---

✅ 요구사항

• 무료 체험 시작 후 14일 이내에 유료 전환한 유저를 전환 유저로 간주
• 월별(event_time) 기준으로:
  • 무료 체험 시작 유저 수 (trial_users)
  • 그 중 14일 이내에 유료 전환한 유저 수 (converted_users)
  • 전환율 (converted_users / trial_users), 소수점 둘째 자리까지

---

✅ 출력 예시

monthtrial_usersconverted_usersconversion_rate

2022-07	123	91	0.74
2022-08	110	66	0.60

WITH free_trial AS (
    SELECT user_id, MIN(event_time) AS MONTH
    FROM subscriptions
    WHERE event_type = 'free_trial_start'
    GROUP BY user_id),
    
    conversion_users AS(
    SELECT user_id, MIN(event_time) AS MONTH
    FROM subscriptions
    WHERE event_type = 'paid_subscribe'
    GROUP BY user_id),
    
    converted_users AS (
    SELECT A.user_id AS trial_users,
           CASE WHEN DATEDIFF(B.MONTH, A.MONTH) <= 14 THEN 1
           ELSE 0
           END AS binary,
           DATE_FORMAT(A.MONTH, '%Y-%m') AS event_month
    FROM free_trial A
    LEFT JOIN conversion_users B ON A.user_id = B.user_id
    )
                      
SELECT  COUNT(DISTINCT trial_users) AS trial_users
       ,SUM(binary) AS converted_users
       ,ROUND(SUM(binary) / COUNT(DISTINCT trial_users),2) AS converted_rate
FROM converted_users
GROUP BY DATE_FORMAT(event_month, '%Y-%m')

꼭 다시 풀자!!!!!!!!!!!!!!!

 

🟦 문제 E: 장바구니 담고 구매하지 않은 유저 분석

✅ 테이블

user_events(user_id, event_type, event_time)

• event_type: 'browse', 'add_to_cart', 'purchase' 중 하나

---

✅ 문제 설명

1. 유저가 장바구니에 상품을 담은 시점 (add_to_cart) 이후
2. 24시간 내에 구매 (purchase)가 없으면 → 이탈로 간주
3. 월별(event_time) 기준으로 다음을 구하시오:

• 장바구니 유저 수
• 이탈 유저 수
• 이탈율 = 이탈 유저 수 / 장바구니 유저 수 (소수점 둘째 자리까지)

WITH cart AS (
    SELECT user_id, MIN(event_time) AS time
    FROM user_events
    WHERE event_type = 'add_to_cart'
    GROUP BY user_id),
    
    purchase AS (
    SELECT user_id, MIN(event_time) AS time
    FROM user_events
    WHERE event_type = 'purchase'
    GROUP BY user_id),
    
    out_users AS (
    SELECT DISTINCT A.user_id AS users, CASE 
        WHEN (TIMESTAMPDIFF(HOUR, A.time, B.time) <= 24) THEN 0 -- 전환
        ELSE 1 -- 이탈
        END AS converted,
        DATE_FORMAT(A.time,"%Y-%m") AS TIME
    FROM cart A LEFT JOIN purchase B ON A.user_id = B.user_id)

SELECT COUNT(users) AS cart_users,
       SUM(converted) AS exit_users,
       ROUND(SUM(converted)/COUNT(users),2)
FROM out_users
GROUP BY TIME

 

핵심 1. out_users CTE절에서 DATE를 CART인지, PURCHASE 인지 확실하게 알기

핵심 2. 이탈 유저 수 -> 전환자의 여집합으로 풀이 -> why? 이탈 유저 수의 조건이 한 개가 아님. (24시간 이후 구매 및 장바구니만 담은 고객 (NULL) + ..) 따라서 전환자 1 ELSE 0 후 SUM으로 집계

 

 

+ 프로그래머스 문제)

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

프로그래머스

 

첫 풀이

WITH BUYERS_2021 AS (
    SELECT DISTINCT  A.USER_ID AS BUYERS
                    ,B.SALES_DATE AS SALES_DATE
    FROM USER_INFO A
    INNER JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID
    WHERE YEAR(A.JOINED) = 2021),
 
    BUYERS_ALL AS (
    SELECT DISTINCT USER_ID AS ALL_USERS, JOINED
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021)
    
SELECT   YEAR(A.SALES_DATE) AS YEAR
        ,MONTH(A.SALES_DATE) AS MONTH
        ,COUNT(A.BUYERS) AS PURCHASED_USERS
        ,ROUND( (COUNT(A.BUYERS) / COUNT(B.ALL_USERS)) , 2) AS PUCHASED_RATIO
        
FROM BUYERS_2021 A
JOIN BUYERS_ALL B ON A.BUYERS = B.ALL_USERS

GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

 

문제점 -> 분모 (2021년 가입한 전체 유저가 JOIN이후 바뀌어버림!)

 

해결 -> 서브쿼리로 고정 + 쓸데없이 마지막 SELECT 문에서 CTE 두개 합치지 않기

WITH BUYERS_2021 AS (
    SELECT DISTINCT  A.USER_ID AS BUYERS
                    ,B.SALES_DATE AS SALES_DATE
    FROM USER_INFO A
    INNER JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID
    WHERE YEAR(A.JOINED) = 2021),
 
    BUYERS_ALL AS (
    SELECT DISTINCT USER_ID AS ALL_USERS, JOINED
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021)
    
SELECT   YEAR(A.SALES_DATE) AS YEAR
        ,MONTH(A.SALES_DATE) AS MONTH
        ,COUNT(A.BUYERS) AS PURCHASED_USERS
        ,ROUND( (COUNT(A.BUYERS) / (SELECT COUNT(*) FROM BUYERS_ALL)) , 2) AS PUCHASED_RATIO
        
FROM BUYERS_2021 A

GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH