LeetCode 코딩 테스트 - Analyze Subscription Conversion(LV.Medium)

 

 

Table: UserActivity

+------------------+---------+
| Column Name      | Type    | 
+------------------+---------+
| user_id          | int     |
| activity_date    | date    |
| activity_type    | varchar |
| activity_duration| int     |
+------------------+---------+
(user_id, activity_date, activity_type) is the unique key for this table.
activity_type is one of ('free_trial', 'paid', 'cancelled').
activity_duration is the number of minutes the user spent on the platform that day.
Each row represents a user's activity on a specific date.

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.

 

Example:

Input:

UserActivity table:

+---------+---------------+---------------+-------------------+
| user_id | activity_date | activity_type | activity_duration |
+---------+---------------+---------------+-------------------+
| 1       | 2023-01-01    | free_trial    | 45                |
| 1       | 2023-01-02    | free_trial    | 30                |
| 1       | 2023-01-05    | free_trial    | 60                |
| 1       | 2023-01-10    | paid          | 75                |
| 1       | 2023-01-12    | paid          | 90                |
| 1       | 2023-01-15    | paid          | 65                |
| 2       | 2023-02-01    | free_trial    | 55                |
| 2       | 2023-02-03    | free_trial    | 25                |
| 2       | 2023-02-07    | free_trial    | 50                |
| 2       | 2023-02-10    | cancelled     | 0                 |
| 3       | 2023-03-05    | free_trial    | 70                |
| 3       | 2023-03-06    | free_trial    | 60                |
| 3       | 2023-03-08    | free_trial    | 80                |
| 3       | 2023-03-12    | paid          | 50                |
| 3       | 2023-03-15    | paid          | 55                |
| 3       | 2023-03-20    | paid          | 85                |
| 4       | 2023-04-01    | free_trial    | 40                |
| 4       | 2023-04-03    | free_trial    | 35                |
| 4       | 2023-04-05    | paid          | 45                |
| 4       | 2023-04-07    | cancelled     | 0                 |
+---------+---------------+---------------+-------------------+

Output:

+---------+--------------------+-------------------+
| user_id | trial_avg_duration | paid_avg_duration |
+---------+--------------------+-------------------+
| 1       | 45.00              | 76.67             |
| 3       | 70.00              | 63.33             |
| 4       | 37.50              | 45.00             |
+---------+--------------------+-------------------+

 

# 쿼리를 작성하는 목표, 확인할 지표 : free_trial에서 paid로 전환한 사람들의 free_trial / paid 별 평균 사용 시간 구하기 / activity_type, activity_date, activity_duration
# 쿼리 계산 방법 : 1. free_trial -> paid로 전환한 유저 구하기 -> 2. 이들 중에서 free_trial or paid 별 평균 사용 시간 구하기 -> 3. 정렬
# 데이터의 기간 : x
# 사용할 테이블 : useractivity
# JOIN KEY : x
# 데이터 특징 : x
WITH base AS (
    SELECT
        user_id,
        activity_type,
        activity_duration
    FROM useractivity
    # 1
    WHERE
        user_id IN (SELECT user_id FROM useractivity WHERE activity_type = 'free_trial') AND
        user_id IN (SELECT user_id FROM useractivity WHERE activity_type = 'paid')
), ft AS (
    # 2
    SELECT
        user_id,
        AVG(activity_duration) AS trial_avg_duration
    FROM base
    WHERE
        activity_type = 'free_trial'
    GROUP BY
        user_id
), p AS (
    # 2
    SELECT
        user_id,
        AVG(activity_duration) AS paid_avg_duration
    FROM base
    WHERE
        activity_type = 'paid'
    GROUP BY
        user_id
)
SELECT
    ft.user_id,
    ROUND(ft.trial_avg_duration, 2) AS trial_avg_duration,
    ROUND(p.paid_avg_duration, 2) AS paid_avg_duration
FROM ft
INNER JOIN p
ON ft.user_id = p.user_id
# 3
ORDER BY
    user_id