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

#留存率 次日继续刷题的用户/第一天刷题的用户
select 
avg(if(datediff(date2,date1)=1,1,0)) avg_ret
from 
(
    select 
     device_id
    ,date date1
    ,lead(date)over(partition by device_id order by date) date2 
    from
    (
        select 
        distinct device_id 
        ,date
        from question_practice_detail
    ) a
) b