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

改进：针对即使是跨多天的情况也没问题！

使用到recursive 递归函数

 with recursive b as (  #活跃用户表。使用递归函数，显示出用户的所有in_time-进入时间和out_time-离开时间的日期（即使跨多天，也没问题！）
select uid,date(in_time) as dt,date(out_time) as ddt from tb_user_log
union all 
select uid,dt,date_sub(ddt,interval 1 day)
from b where ddt>dt
)

select a.min_dt as dt,
round(count(distinct b.uid)/count(distinct a.uid),2) as uv_left_rate
from (
    select uid,min(date(in_time)) as min_dt   #2021年11月的新用户表
    from tb_user_log
    group by uid
    having min(date(in_time)) between '2021-11-01' and '2021-11-30'
) as a
left join b                       #左连接          
on datediff(b.ddt,a.min_dt)=1 and a.uid=b.uid  #两个连接条件！
group by a.min_dt 
order by dt