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

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
    ) as p1
left join 
    (select 
        distinct device_id, date from question_practice_detail
    ) as p2
on 
    p1.device_id=p2.device_id
    and p2.date=date_add(p1.date,interval 1 day);

【好难啊啊啊啊啊啊啊啊啊啊啊——————】

解题思路

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

1. 确定每个用户每日的刷题记录：首先，获取每个用户在每个日期的刷题记录，确保每个用户每天只被计算一次。
2. 判断用户次日是否有刷题记录：对于每个用户在某一天的刷题记录，检查用户在次日是否有刷题行为。如果有，则认为该用户在次日有留存。
3. 计算留存率：留存率的计算公式为： 最终结果保留四位小数。

代码解释

• 子查询 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)：将留存率结果保留四位小数。