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

with
r as (
    select 
    device_id,
    date_add(date, interval 1 day) as next_day
    from question_practice_detail
)
, b as (
    select 
    a.device_id, 
    a.date,
    count(distinct(a.device_id)) as total_cnt,
    count(distinct(case when r.next_day is not null then a.device_id end)) as retention_cnt
    from question_practice_detail as a
    left join r on r.device_id = a.device_id 
    and r.next_day = a.date
    group by 1,2
)

select 
round(sum(retention_cnt)/sum(total_cnt),4) as avg_ret
from b 
;

本题是大厂SQL面试中常考的留存率问题，对于留存率问题的一般解题思路如下：

（1）找到首次登录时间T；

（2）找T+N对应时间是否有登录记录，有则留存，否则流失；

（3）统计步骤（2）对应计数项。

其中，主要考察使用到日期函数和聚合函数。

本题中，并非一般的首次登录留存，而是二次答题率，因此对于去重的考虑并非是单个用户只保留一条记录，而是单个用户、当天作为一条去重记录保存：count(distinct(a.device_id)) as total_cnt

关于利用left join连接的约束，找到后一天有答题记录的用户。