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

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
    ) b
    on a.device_id=b.device_id
    and date_add(a.date, interval 1 day)=b.date
) c