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

select a.dt,round(count(b.uid)/count(a.uid),2) as uv_left_rate
from (
-- 用户的最小日期
select uid,min(date(in_time)) as 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) dt 
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