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

• 题意

用户在某天刷题后第二天再来刷题的平均概率

问题分解：

• 限定条件：第二天再来。
• 解法1：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是同一个用户。
• 解法2：用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id，再按日期升序排序order by date，再两两拼接（最后一个默认和null拼接），即lead(date) over (partition by device_id order by date)
• 平均概率：解法1：可以count(date1)得到左表全部的date记录数作为分母，count(date2)得到右表关联上了的date记录数作为分子，相除即可得到平均概率解法2：检查date2和date1的日期差是不是为1，是则为1（次日留存了），否则为0（次日未留存），取avg即可得平均概率。
• 附：lead用法，date_add用法，datediff用法，date函数

select count(date2)/count(date1) as avg_ret
from(
    select distinct a.device_id,a.date date1,b.date date2
    from question_practice_detail a
    left join(
        select distinct device_id,date
        from question_practice_detail
    ) as b
    on a.device_id=b.device_id and date_add(a.date,interval 1 day)=b.date
) as d

细节问题：

• 表头重命名：as
• 去重：需要按照devece_id,date去重，因为一个人一天可能来多次
• 子查询必须全部有重命名