with td as ( select id,uid,date(in_time) as indate,date(out_time) as outdate from tb_user_log ), tnew as ( select t.indate,count(t.uid) as nb_new from td t where not exists ( select 1 from td tback where tback.uid=t.uid and tback.indate < t.indate ) group by t.indate ) select t.indate, round(sum(case when exists( select 1 from td tafter where t.uid=tafter.uid and datediff(tafter.indate,t.indate)=1 ) or (datediff(t.outdate,t.indate)=1) then 1 else 0 end) /avg(tn.nb_new),2) from td t left join tnew tn on t.indate=tn.indate where not exists ( select 1 from td tback where tback.uid=t.uid and tback.indate < t.indate ) and t.indate between'2021-11-1' and '2021-11-30' group by t.indate order by t.indate asc