LeetCode - 3497. Analyze Subscription Conversion (Oracle)

Medium - 인라인 뷰

RunTime : 364 ms

문제

A subscription service wants to analyze user behavior patterns. The company offers a 7-day free trial, after which users can subscribe to a paid plan or cancel. Write a solution to:

1. Find users who converted from free trial to paid subscription
2. Calculate each user's average daily activity duration during their free trial period (rounded to 2 decimal places)
3. Calculate each user's average daily activity duration during their paid subscription period (rounded to 2 decimal places)

Return the result table ordered by user_id in ascending order.

The result format is in the following example.

풀이

• 새로 등록된 Medium 문제, 빠르게 풀어봤다.

1. CASE 절로 각 activity_type에 맞는 값들의 평균을 구해준다.
2. activity_type이 free_trial과 paid중 하나에 속하면서, 현재 activity_type이 paid인 user_id를 찾는다.
   • 초기가 free_trial이기 때문에 가능
3. user_id별로 GROUP BY 한 후, 정렬

SELECT
    user_id,
    ROUND(AVG(
        CASE 
            WHEN activity_type = 'free_trial' THEN activity_duration 
        END)
    , 2) AS trial_avg_duration,
    ROUND(AVG(
        CASE 
            WHEN activity_type = 'paid' THEN activity_duration
        END)
    , 2) AS paid_avg_duration
FROM UserActivity
WHERE activity_type IN ('free_trial', 'paid')
  AND user_id IN (
        SELECT user_id
        FROM UserActivity
        WHERE activity_type = 'paid'
    )
GROUP BY user_id
ORDER BY user_id;