# 对原表进行处理，因为涉及到跨天活跃
with a as
(select uid,in_time
from (
SELECT uid,date(in_time) as in_time from tb_user_log
UNION ALL
SELECT uid,date(out_time) as in_time  from tb_user_log)t
group by uid,in_time) 
select t3.in_time,round(ifnull(t4.cnt_next/t3.cnt_first,0.00),2) as uv_left_rate from (
# 计算每个日期的新增用户数
select t1.in_time,count(*) as cnt_first from (
select u1.uid,DATE_FORMAT(u1.in_time,'%Y-%m-%d') as in_time,
row_number() over(partition by u1.uid order by DATE_FORMAT(u1.in_time,'%Y-%m-%d') ) as rank_date 
from a as u1 ) t1 where t1.rank_date=1 and year(t1.in_time)=2021 
and month(t1.in_time)=11 group by t1.in_time 
) t3 
left join 
(
# 计算每个日期的次日用户数
select t2.in_time,count(*) as cnt_next from (
select u1.uid,DATE_FORMAT(u1.in_time,'%Y-%m-%d') as in_time,
row_number() over(partition by u1.uid order by DATE_FORMAT(u1.in_time,'%Y-%m-%d') ) as rank_date,
datediff(lead(DATE_FORMAT(u1.in_time,'%Y-%m-%d'),1) over(partition by u1.uid order by DATE_FORMAT(u1.in_time,'%Y-%m-%d')),DATE_FORMAT(u1.in_time,'%Y-%m-%d')) as lead_date_1 
from a as u1 ) t2 where  year(t2.in_time)=2021 
and month(t2.in_time)=11 and t2.rank_date=1 and t2.lead_date_1=1 group by t2.in_time 
) t4 
on t3.in_time=t4.in_time order by t3.in_time ASC