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

with  a as (
    select uid ,date(in_time)  dt
    from tb_user_log
    union 
    select uid ,date(out_time)  dt
    from tb_user_log 
)-- users active  time

select dt, round(sum(if(datediff(next_dt, dt)=1,1,0)) / count(distinct uid),2) uv_rate
from (
    select uid, dt,
           lead(dt,1)  over(partition by uid order by dt) next_dt,
           row_number() over(partition by uid order by dt) dt_rank
    from a ) b
where dt_rank = 1 and date_format(dt,'%Y-%m') = '2021-11'
group by dt
order by dt;

select t1.dt,ifnull(round(count(distinct t2.uid)/count(t1.uid),2),0) uv_rate
from (
    select uid,min(date(in_time)) dt
    from tb_user_log
    group by uid ) t1 -- new users
left join (
    select uid ,date(in_time) dt
    from tb_user_log
    union
    select uid ,date(out_time) dt
    from tb_user_log ) t2 -- users active time
on t1.uid = t2.uid and t1.dt = date_sub(t2.dt,interval 1 day)
where t1.dt like '2021-11%'
group by t1.dt
order by t1.dt;