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

with T as( # 用户在某天活跃与下一次活跃

select uid, time,
    lead(time)over(partition by uid order by time) as next_time
    from (
        select uid,
        date(in_time) as time,
        1 as is_active
        from tb_user_log

        union

        select uid,
        date(out_time) as time,
        1 as is_active
        from tb_user_log
        order by uid, time
    )t
    
),
B as (  # 每日新用户有那些
    select 
    distinct   date(min(in_time)over(partition by uid)) as first_log,
    uid
    from tb_user_log
)

select
B.first_log as dt, 
round(count(T.next_time)/count(*) , 2) as uv_left_rate
from B
left join T
on B.first_log=T.time and B.uid=T.uid 
where date_format(B.first_log,'%Y-%m') = '2021-11' and (T.next_time is null or
timestampdiff(day, time, next_time)=1
)
group by dt
order by dt