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

select min_time,round(count(dt)/count(min_time),2) uv_left_rate
from (
select uid,min(date(in_time)) min_time
from tb_user_log
group by uid) t1 # 新用户表
left join(
select uid,date(in_time) dt
from tb_user_log
union
select uid,date(out_time) dt
from tb_user_log  ) t2 #活跃用户表
on t2.uid = t1.uid and datediff(t2.dt,t1.min_time)=1
where date_format(min_time,'%Y-%m')='2021-11'
group by min_time
order by min_time