[Leetcode] Analyze Subscription Conversion

문제 링크

정답

WITH F_TO_P_USERS AS (
    SELECT
        user_id
    FROM UserActivity
    WHERE activity_type IN ('free_trial', 'paid')
    GROUP BY user_id
    HAVING COUNT(DISTINCT activity_type) > 1
)

SELECT
    T1.user_id, 
    ROUND(
        AVG(
            CASE WHEN T2.activity_type = 'free_trial' THEN T2.activity_duration END
        ) 
    , 2) 
    as trial_avg_duration,
    ROUND(
        AVG(
            CASE WHEN T2.activity_type = 'paid' THEN T2.activity_duration END
        )
    , 2) 
    as paid_avg_duration    
FROM F_TO_P_USERS T1
JOIN UserActivity T2
ON T1.user_id = T2.user_id
GROUP BY T2.user_id
ORDER BY 1;

✅ Point

• 조건 1: activity_type이 free_trial에서 paid로 전환된 유저
  • CTE로 선언
  • WHERE activity_type IN ('free_trial', 'paid')
    • 둘 중 하나의 상태를 가지면서
  • HAVING COUNT(DISTINCT activity_type) > 1
    • 두 개 이상의 상태를 가지는 경우
  • 두 조건을 통해 free_trial > cancelled로 전환되는 경우를 배제할 수 있음
• 위에서 선언한 CTE와 기존 table을 join 하여 조건을 만족하는 user만 다루기
  • join을 하면 하나의 user에 대해 여러 기록이 존재
  • 앞으로 필요한 값은 user 별 특정 activity type에 대한 평균값
    • => GROUP BY 수행
• 조건 2: free_trial 동안의 activity_duration 평균값
  • CASE WHEN 문을 통해 activity_type이 free_trial인 경우 filtering
    • 이때, ELSE 0 을 쓰면 안됨! (평균값을 산출하는데 영향을 줌)
• 조건 3: paid 동안의 activity_duration 평균값
  • 조건 2와 동일함