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

select min(t2.count1)/max(t2.count1)
from(
    select count(*) as count1
        from
            (select  t.device_id, t.date,
                    lead (t.date, 1) over ( partition by t.device_id
                          order by  t.date ) as date1
            from question_practice_detail t  
            ) t1
        where   t1.date1 - t1.date = 1
        union all
        select count(*) as count1
        from   (select distinct device_id , date
                  from question_practice_detail) t 
        ) t2

注意计算总数的时候需要去重