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

with t1 as(
    select uid,date(date_sub(in_time,interval 1 day)) dt2
    from tb_user_log
    union
    select uid,date(date_sub(out_time,interval 1 day)) dt2
    from tb_user_log   
),  
t2 as(
    select uid,date(min(in_time)) dt
    from tb_user_log
    group by uid

) 
select dt ,round(count(dt2)/count(*),2) uv_left_rate from t2
left join t1
on t1.dt2=t2.dt and t1.uid=t2.uid
where date_format(dt,'%Y%m')='202111'
group by dt