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

with tb as (
select uid,dt ,
min(dt) over(partition by uid) as new_dt, #按照uid分组求每个uid 最小日期
lead(dt,1) over(partition by uid order by dt) as next_dt  #偏移作为次日 有无order都行
from 
    (select distinct uid ,date(in_time) as dt
    from tb_user_log
    union 
    select distinct uid,date(out_time) as dt
    from tb_user_log) as tb_active
)

select dt, 
round(sum(case when dt=new_dt and datediff(next_dt,dt)=1 then 1 else 0 end)/#dt=new_dt那这个用户为新用户
sum(case when dt=new_dt then 1 else 0 end ),2) as uv_left_rate
                                        # dt=new_dt且next_dt和new_dt的日期差为1则这个用户为次留新用户
from tb
where date_format(dt,'%Y%m') = 202111
group by dt
having uv_left_rate is not null
order by dt

# select dt,
# round(sum(if(timestampdiff(day,dt,lead_time)=1,1,0))/count(*),2) 
# from(
#     select *, 
#     row_number() over(partition by uid order by dt) rk, 
#     lead(dt,1) over(partition by uid order by dt) lead_time from( select uid,date(in_time) as dt 
#     from tb_user_log 
#     union 
#     select uid,date(out_time) as dt 
#     from tb_user_log )t1)t2 
# where rk = 1
# group by dt 
# having date_format(dt,'%Y-%m') = '2021-11' 
# order by dt