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

#问题：统计2021年11月每天新用户的次日留存率（保留2位小数）
#1.2021年11月 每天
#2.新用户 次日留存率 当天新增的用户数中第二天又活跃了的用户数占比

select in_tm,round(count(distinct l_1)/count(uid),2) uv_left_rate
from
(select uid,in_tm,
         if((case when timestampdiff(day,in_tm,out_tm)>0 then 1 else 0 end)+(case when timestampdiff(day,in_tm,l_in_tm)>0 then 1 else 0 end)=0,null,uid) l_1
from
(select distinct uid, 
      date_format(in_time,'%Y-%m-%d') in_tm,
      date_format(out_time,'%Y-%m-%d') out_tm,
      lead(date_format(in_time,'%Y-%m-%d'),1,0) over(partition by uid) l_in_tm,
      case when date_format(in_time,'%Y-%m-%d') = min(date_format(in_time,'%Y-%m-%d'))over(partition by uid) then 1 else null end new
from tb_user_log)data1
where new is not null)data2
where year(in_tm)=2021 and month(in_tm)=11
group by in_tm