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

select
    count(case when diff = 1 then device_id else null end) / count(device_id) as avg_ret
from (
    select*,(lead (date, 1) over (partition by device_id order by date) - date) as diff
    from (
        select distinct device_id,date
        from question_practice_detail
    ) t1
) t2