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

with t1 as(select uid,min(DATE(in_time)) as dt from tb_user_log  group by uid),

t2 as(select uid,date(in_time) as dt from tb_user_log
UNION
select uid,date(out_time) as dt from tb_user_log
order by uid,dt),

t3 as(select t1.* ,t2.dt as dt1 FROM
t1 left join t2 on t1.uid=t2.uid and t1.dt=DATE_SUB(t2.dt,INTERVAL 1 day))

select dt,round(count(dt1)/count(dt),2) as uv_left_rate   from t3
where DATE_FORMAT(dt,'%Y-%m')='2021-11' group by dt