题解 | #日活与每日次日留存率#

select
    t1.visit_date,
    t1.dau,
    round(
        if (t2.next_day_per is not null, t2.next_day_per, 0),
        4
    ) next_day_per
from
    (
        select
            visit_date,
            (count(*)) dau
        from
            user_visit_log
        group by
            visit_date
    ) t1
    left join (
        select
            u1.visit_date visit_date,
            (
                count(*) / (
                    select
                        count(*)
                    from
                        user_visit_log
                    where
                        visit_date = u1.visit_date
                )
            ) next_day_per
        from
            user_visit_log u1
            join user_visit_log u2 on u1.id = u2.id
            and datediff (u2.visit_date, u1.visit_date) = 1
        group by
            u1.visit_date
    ) t2 on t1.visit_date = t2.visit_date