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

#对原始数据进行去重
with data as
(
    select distinct
    device_id
    ,date
    from question_practice_detail
)

select
sum(if(datediff(next_date,date)=1,1,0))/count(device_id) as avg_ret
from
(
    select
    device_id
    ,date
    #每一个日期跟一个next_date,没有的话用0补充，确保后续count正确
    ,ifnull(lead(date,1)over(partition by device_id order by date),0) as next_date
    from data
)a