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

# 日活dau = 当日登入总用户数 
# 每日次日留存率 = 连续登入用户数 / dau
select
    visit_date,
    count(id) as dau,
    sum(if(datediff(next_date,visit_date) = 1, 1, 0)) / count(id) as next_day_per
from
    (
        select
            id,
            visit_date,
            lead(visit_date,1) over ( partition by id order by visit_date ) as next_date
        from
            user_visit_log
    ) res
group by
    visit_date
order by 
    visit_date
;