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

from(select md,cc,ifnull(ss,0)sss
from(select ot,sum(b)ss
from(select uid,ot,
       case when (uid,dt) in
                 (select da.uid,daa
                         from(select uid,date_add(min(date(in_time)),interval 1 day)daa
           from tb_user_log
           group by uid)da right join tb_user_log tl on da.uid=tl.uid
           where daa=date(in_time)) then 1
           #限定第一个条件，当某用户最早的登录日期的下一天存在于登陆日期中时，计1分
           when ot>dt  then  1
           #限定第二个条件，当某用户跨天退出时，计1分
           #选择离开时间作为计分条件列可以涵盖当天离开和跨天离开的情况
           else 0 end as b
from(select uid,date(in_time)dt,date(out_time)ot
from tb_user_log)a)a2
group by ot)b1
#b1记录的是次日还登陆的新用户数量
right join
(select md,COUNT(*)cc
from (select uid,min(date(in_time))md
from tb_user_log
group by uid)a3
group by md)b2
#b2记录的是每天新用户数量
on b1.ot=date_add(b2.md,interval 1 day )#错行匹配，方便最终的比率运算
)bbb
where round(sss/cc,3)is not null
order by md