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

with tmp as (
    select distinct t1.uid,date(in_time) login_date,date(out_time) as out_date,t2.min_date
   from tb_user_log t1
   left join (select uid,min(date(in_time)) as min_date from tb_user_log group by uid) t2
  on t1.uid = t2.uid
    where substr(date(in_time),1,7) = '2021-11' and substr(min_date,1,7) = '2021-11'
)
# select * from tmp;
select min_date,
       round(sum(if(datediff(login_date,min_date)=1,1,0) or if(datediff(out_date,login_date)=1,1,0)) / sum(if(datediff(login_date,min_date)=0,1,0)),2) uv_left_rate
#        sum(if(datediff(login_date,min_date)=1,1,0)) , sum(if(datediff(login_date,min_date)=0,1,0))
from tmp
group by min_date
order by min_date
;

注意：如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过