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

一、窗口函数

select
    dt
    ,round(sum(if(rk=1 and datediff(lead_dt,dt)=1,1,0))/sum(if(rk=1,1,0)),2) uv_left_rate
from (
    select
        dt
        ,row_number()over(partition by uid order by dt) rk
        ,lead(dt,1)over(partition by uid order by dt) lead_dt
    from (
        select
            uid, date(in_time) dt
        from tb_user_log
        union
        select
            uid, date(out_time) dt
        from tb_user_log
    ) t
) t
group by 1
having uv_left_rate is not null and date_format(dt,'%Y-%m')='2021-11'
order by 1

二、连接代替窗口

select t1.dt,round(count(t2.uid)/count(t1.uid),2) uv_rate
from (select uid
      ,min(date(in_time)) dt
      from tb_user_log 
      group by uid) as t1  -- 每天新用户表
left join (select uid , date(in_time) dt
           from tb_user_log
           union
           select uid , date(out_time)
           from tb_user_log) as t2 -- 用户活跃表
on t1.uid=t2.uid
and t1.dt=date_sub(t2.dt,INTERVAL 1 day)
where date_format(t1.dt,'%Y-%m') = '2021-11'
group by t1.dt
order by t1.dt