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

#2021 11
#新用户
#第二天是否回来--->datediff=1
#跨天也行--> union
with tnew as (
    select distinct t.uid, date(in_time) as regtime
    from tb_user_log t
    where date(in_time) between '2021-11-01' and '2021-11-30'
    and not exists(
        select 1 
        from tb_user_log tbefore
        where tbefore.uid=t.uid and  date(tbefore.in_time)<date(t.in_time)
    )
),
tnbnew as (
    select tn.regtime as regtime,count(uid) as nbnew
    from tnew tn
    group by (tn.regtime)

),
talltrace as (
    select distinct t1.uid, date(t1.in_time) as date
    from tb_user_log t1 
    union 
    select distinct t2.uid, date(t2.out_time) as date
    from tb_user_log t2 
)


select  tn.regtime as dt,
        round(sum(case when datediff(ta.date,tn.regtime)=1 then 1 else 0 end)/max(tnew.nbnew),2) as uv_left_rate
from talltrace ta   join tnew tn on ta.uid = tn.uid 
                    join tnbnew tnew on tn.regtime=tnew.regtime
group by tn.regtime