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

select
    avg(if(datediff(date2, date)=1, 1, 0)) as avg_ret
from
    (
        select distinct
            device_id,
            date,
            lead(date) over (
                partition by
                    device_id
                order by date
            ) as date2
        from
            (
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            ) as unique_id_date
    ) t