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

with t1 as(

select *,lead(visit_date,1) over(partition by id order by visit_date) next_date from  user_visit_log)

select visit_date, count(distinct id) dau,sum(if(datediff(next_date,visit_date)=1,1,0))/count(distinct id) next_day_per

from t1

group by visit_date;