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

from(select uid,date(out_time)ot,
    case when date(in_time)!=date(out_time) then -1
            else 1 end as cnt
from tb_user_log)a2#标记当天活跃且不跨天的用户为1分
union all
select uid,ot,cnt
from(select uid,date(out_time)ot,
       case when date(in_time)!=date(out_time) then 1
            else -1 end as cnt
from tb_user_log)a1#标记当天活跃且跨天的用户为1分
where cnt>0) )#存放临时表，内容是日活跃用户情况
select ot,sc,round(count(u)/sc,2)#将u联结在表后，就可以知道每个日期下的新用户是谁，对u列基于ot分组计数就可以得到每个日期下的新用户人数
from(select ot,sum(cnt)sc
from a3
group by ot)b1#日活总数
left join
(select uid as u,min(ot) ft
from a3
group by uid)b2 on b2.ft=b1.ot#找出每个日期下的新用户是哪位
group by ot