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

select  
    register_time,round(count(time)/count(*),2) 
    from (
    (select date_format(min(in_time),'%Y-%m-%d') register_time,uid as uid from tb_user_log    
        group by uid )t1	#每个uid的最早注册时间
    left join
    (select * from 
    (select uid,date_format(in_time,'%Y-%m-%d') as time from  tb_user_log
        union  
    select uid,date_format(out_time,'%Y-%m-%d') as time  from  tb_user_log
    ) ts
    ) t2
    on t1.uid = t2.uid and  t1.register_time=date_sub(t2.time,INTERVAL 1 day)
    )
    where left(register_time,7) = '2021-11'
    group by register_time


# select *
# 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

这道题蛮难的，需要对每个求出每个id以及对应的注册时间表，然后再left join 增加是否在下一天活跃等信息，根据相关count求出比例。