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

 -- 先根据每个用户最初活跃时间，来统计每天的新用户信息
 -- 再进行日期+1，获取对应的用户信息，与新用户信息进行连接
select t1.dt ,round(count(t2.uid)/count(t1.uid),2) as uv_rate 
from 
-- 新用户信息表
(select uid,min(date(in_time)) dt 
from tb_user_log  
group by uid ) t1
left join (
-- 用户信息全量表
 select uid,date(in_time) as dt 
 from tb_user_log 
 union 
 select uid,date(out_time) as dt 
 from tb_user_log ) t2 
 on t1.uid = t2.uid 
 and t1.dt = date_sub(t2.dt,interval 1 day)  
 where date_format(t1.dt,'%Y-%m') = '2021-11' 
 group by t1.dt 
 order by t1.dt