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

with 
t1 as(---11月所有活跃日期
    select dates
    from
    ((select date(in_time) as dates
     from tb_user_log
     group by date(in_time))
    union
    (select date(out_time) as dates
     from tb_user_log
     group by date(out_time))) a
    where left(dates,7)='2021-11'),
t2 as(---11月所有活跃日期的活跃名单    
    select
        t1.dates,
        t0.uid
    from t1 left join tb_user_log t0 on (t1.dates=date(t0.in_time) or t1.dates=date(t0.out_time))
    where t0.id is not null
    group by t1.dates,t0.uid),
t3 as(---所有活跃日期
    select dates
    from
    ((select date(in_time) as dates
     from tb_user_log
     group by date(in_time))
    union
    (select date(out_time) as dates
     from tb_user_log
     group by date(out_time))) a),
t4 as(---所有活跃日期的活跃名单
    select
        t3.dates,
        t0.uid
    from t3 left join tb_user_log t0 on (t3.dates=date(t0.in_time) or t3.dates=date(t0.out_time))
    where t0.id is not null
    group by t3.dates,t0.uid),
t5 as(---11月所有活跃日期，非首次登录的用户
    select 
        t2.dates,t2.uid
        from t2 join t4 on t2.dates>t4.dates and t2.uid=t4.uid group by t2.dates,t2.uid),
t6 as(---11月所有活跃日期，每日首次登录的用户
    select
        t2.dates,t2.uid
    from t2 where t2.uid not in(select t5.uid from t5 where t5.dates=t2.dates)),
t7 as(---11月所有活跃日期，每日首次登录且次日也登录的用户
    select
        a.dates,a.uid
    from t6 a join t2 b on date_add(a.dates,interval 1 day)=b.dates and a.uid=b.uid)
t8 as(---最终需要的日期、分母   
    select
        dates,
        count(distinct uid) as num1
    from t6 group by dates),
t9 as(---最终需要的日期、分子 
    select
        dates,
        count(distinct uid) as num2
    from t7 group by dates)

select
    t8.dates,
    ifnull(round(t9.num2/t8.num1,2),0) as uv_left_rate
from t8 left join t9 on t8.dates=t9.dates
order by t8.dates