题解 | #计算用户的平均次日留存率#

题目描述

运营团队希望查看用户在某天刷题后第二天是否会继续刷题的留存率。具体来说，计算所有用户在某天进行刷题活动后，第二天还进行刷题的用户比例，并求其平均值。需要从 question_practice_detail 表中提取相关数据，最终返回平均留存率 avg_ret，结果保留四位小数。

解题思路

要计算用户的留存率，我们需要完成以下步骤：

1. 确定每个用户每日的刷题记录：

   • 首先，获取每个用户在每个日期的刷题记录，确保每个用户每天只被计算一次。

2. 判断用户次日是否有刷题记录：

   • 对于每个用户在某一天的刷题记录，检查用户在次日是否有刷题行为。如果有，则认为该用户在次日有留存。

3. 计算留存率：

   • 留存率的计算公式为：
   • 最终结果保留四位小数。

SQL 查询

SELECT 
    ROUND(
        SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) , 
        4
    ) AS avg_ret
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) p1
LEFT JOIN 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) p2
ON 
    p1.device_id = p2.device_id 
    AND p2.date = DATE_ADD(p1.date, INTERVAL 1 DAY);

代码解释

• 子查询 p1 和 p2：

  • p1：提取每个用户每天的唯一刷题记录。
  • p2：同样提取每个用户每天的唯一刷题记录，用于与 p1 进行自连接。

• LEFT JOIN 连接条件：

  • 连接条件为同一 device_id 且 p2.date 是 p1.date 的次日（即 p1.date 加一天）。
  • 通过这种方式，可以判断用户在某天刷题后次日是否有刷题记录。

• SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END)：

  • 统计有次日刷题记录的用户数量。如果 p2.device_id 不为空，说明用户在次日有刷题行为，计数加1。

• COUNT(*)：

  • 统计总的刷题记录数，即总的 (device_id, date) 组合数量。

• ROUND(..., 4)：

  • 将留存率结果保留四位小数。