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

1. 新增用户表：

select 
    uid,
    min(date(in_time)) as dt
from tb_user_log
group by uid

2. 活跃用户表

select 
    uid,
    date(in_time) as dt
from tb_user_log
union 
 select 
    uid,
    date(out_time) as dt
from tb_user_log   

3. 联结两表，条件日期相差1，uid相同

select
    uid,dt
from t1 
left join t2
on t1.dt=t2.dt
and datediff(t2.dt,t1.dt)=1

4. 从上面选出结果

• 条件：11月
• 分组：按照日期分组

  select
    t1.dt,
    round(count(distinct t2.uid)/count(t1.uid),2) as uv_left_rate
  from(
    select 
        uid,
        min(date(in_time)) as dt
    from tb_user_log
    group by uid
    ) t1 
    left join (
    select 
        uid,
        date(in_time) as dt
    from tb_user_log
    union
    select 
        uid,
        date(out_time) as dt
    from tb_user_log 
    )t2
    on t1.uid=t2.uid
    and datediff(t2.dt,t1.dt)=1   
  where date_format(t1.dt,"%Y%m") = '202111'   
  group by t1.dt