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

with zb3 as(with zb1 as(with zb as (select uid,in_time,row_number() over(partition by uid order by in_time) a
from tb_user_log
union all
select uid,out_time,row_number() over(partition by uid order by out_time) 
from tb_user_log
order by uid,in_time)
select *,lead(in_time) over(partition by uid order by in_time) a1
from zb)
select *,date_format(in_time,'%Y-%m-%d') dt,if(datediff(a1,in_time)=1 and a = 1,1,0) xx,row_number() over(partition by uid order by in_time) xx1
from zb1)
select dt,round(sum(xx)/sum(if(xx1=1,1,0)),2) uv_left_rate
from zb3
where in_time like "2021-11%"
group by dt
having uv_left_rate is not null
order by 1