Leetcode 3497. 分析订阅转化

1.题目基本信息

1.1.题目描述

表：UserActivity

+------------------+---------+

| Column Name | Type |

+------------------+---------+

| user_id | int |

| activity_date | date |

| activity_type | varchar |

| activity_duration| int |

+------------------+---------+

(user_id, activity_date, activity_type) 是这张表的唯一主键。

activity_type 是('free_trial', 'paid', 'cancelled')中的一个。

activity_duration 是用户当天在平台上花费的分钟数。

每一行表示一个用户在特定日期的活动。

订阅服务想要分析用户行为模式。公司提供7天免费试用，试用结束后，用户可以选择订阅 付费计划 或 取消。编写解决方案：

1. 查找从免费试用转为付费订阅的用户

2. 计算每位用户在 免费试用 期间的 平均每日活动时长（四舍五入至小数点后 2 位）

3. 计算每位用户在 付费 订阅期间的 平均每日活动时长（四舍五入到小数点后 2 位）

返回结果表以 user_id 升序 排序。

1.2.题目地址

https://leetcode.cn/problems/analyze-subscription-conversion/description/

2.解题方法

2.1.解题思路

分组聚合

2.2.解题步骤

第一步，基于UserActivity表格，按user_id和activity_type进行分组聚合，统计activity_duration的平均每日活动时长（筛掉cancelled的行），得到表格T1

第二步，基于T1分别筛选出activity_type=free_trial和activity_type=paid的行，记为表格T2和T3；然后将T2和T3按user_id进行内连接，即得到题解

3.解题代码

python代码

# Write your MySQL query statement below

WITH T1 AS (
    # 第一步，基于UserActivity表格，按user_id和activity_type进行分组聚合，统计activity_duration的平均每日活动时长（筛掉cancelled的行），得到表格T1
    SELECT 
        user_id, activity_type, 
        ROUND(AVG(activity_duration), 2) AS avg_duration
    FROM UserActivity 
    WHERE activity_type != 'cancelled'
    GROUP BY user_id, activity_type
)

# 第二步，基于T1分别筛选出activity_type=free_trial和activity_type=paid的行，记为表格T2和T3；然后将T2和T3按user_id进行内连接，即得到题解
SELECT 
    user_id, 
    T2.avg_duration AS trial_avg_duration, 
    T3.avg_duration AS paid_avg_duration
FROM 
    (SELECT * FROM T1 WHERE activity_type='free_trial') AS T2 
        INNER JOIN 
    (SELECT * FROM T1 WHERE activity_type='paid') AS T3
    USING(user_id)
ORDER BY user_id

4.执行结果