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

select sum(TF1)/count(TF1) avg_ret from
(select
    case
        when date_add(date, interval 1 day)=n_date then 1
        else 0
    end as TF1
from
    (
        select
            device_id,
            date,
            lead (date) over (
                partition by
                    device_id
                order by
                    date
            ) n_date
        from
            (
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            ) as dis_q
    ) as table2
) as table3