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

with td as (
    select id,uid,date(in_time) as indate,date(out_time) as outdate
    from tb_user_log 
),

tnew as (
    select t.indate,count(t.uid) as nb_new
    from td t
    where not exists (
        select 1 
        from td tback
        where tback.uid=t.uid and tback.indate < t.indate
    )
    group by t.indate
)

select t.indate, round(sum(case when exists(
        select 1
        from td tafter
        where t.uid=tafter.uid and datediff(tafter.indate,t.indate)=1
     ) or (datediff(t.outdate,t.indate)=1) 
     then 1 else 0 end) /avg(tn.nb_new),2)
from td t left join tnew tn on t.indate=tn.indate
where not exists (
        select 1 
        from td tback
        where tback.uid=t.uid and tback.indate < t.indate
    ) 
    and t.indate between'2021-11-1' and '2021-11-30'
group by t.indate
order by t.indate asc