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

select a.visit_date as visit_date,
       count(distinct a.id) as dau,
       count(b.id)/count(distinct a.id) as next_day_per      
from 
(select id,visit_date 
from user_visit_log
group by id,visit_date) a
left join 
(select id,visit_date 
from user_visit_log
group by id,visit_date) b
on a.id=b.id
and a.visit_date = date_sub(b.visit_date,interval 1 day)
group by a.visit_date;