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

select 
   avg(case when datediff(date2,date1)=1 then 1
            else 0 
       end) as avg_ret
from 
    (select 
        device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
    from
        (select distinct device_id, date
        from question_practice_detail) as new_form
    ) as lead_table

从里向外打造需要的表，然后用lead把下一行的时间复制到当行，比较两列的时期差值，最后计算出平均留存率