题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
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-离开时间跨天了,在两天里都记为该用户活跃过