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

with t1 as(SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log)
select first,round(count(dt)/count(uid),2) from (select t2.uid,first,dt from (select uid,min(dt) as first from t1 group by uid) t2 left join t1 on t1.uid=t2.uid and datediff(t1.dt,t2.first)=1 )t3 where first like '2021-11%' group by first order by first