题解 | #2021年11月每天新用户的次日留存率#

select a.dt, 
       ifnull(round(count(distinct b.uid)/count(a.uid),2),0) uv_left_rate
from (select uid,
             min(date(in_time)) dt
      from tb_user_log  
      group by uid) a
left join (select uid , date(in_time) dt
           from tb_user_log
           union 
           select uid , date(out_time)
           from tb_user_log) b
on a.uid = b.uid 
   and a.dt = date_sub(b.dt, INTERVAL 1 day)
where date_format(a.dt,'%Y-%m') = '2021-11'
group by a.dt
order by a.dt