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

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)