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

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);

1. distinct去重
2. 用left join两个表来连接
3. date_add(p1.date, interval 1 day) ： INTERVAL 1 DAY：要添加的时间间隔 ——“1 天”；
4. sum(case when p2.device_id is not null then 1 else 0 end) / count(*) , 4)

• 因为用了 left join ,所以p2.device_id会出现null
• sum(...)：把所有记录的1/0求和