#2021 11 #新用户 #第二天是否回来--->datediff=1 #跨天也行--> union with tnew as ( select distinct t.uid, date(in_time) as regtime from tb_user_log t where date(in_time) between '2021-11-01' and '2021-11-30' and not exists( select 1 from tb_user_log tbefore where tbefore.uid=t.uid and date(tbefore.in_time)<date(t.in_time) ) ), tnbnew as ( select tn.regtime as regtime,count(uid) as nbnew from tnew tn group by (tn.regtime) ), talltrace as ( select distinct t1.uid, date(t1.in_time) as date from tb_user_log t1 union select distinct t2.uid, date(t2.out_time) as date from tb_user_log t2 ) select tn.regtime as dt, round(sum(case when datediff(ta.date,tn.regtime)=1 then 1 else 0 end)/max(tnew.nbnew),2) as uv_left_rate from talltrace ta join tnew tn on ta.uid = tn.uid join tnbnew tnew on tn.regtime=tnew.regtime group by tn.regtime