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

select t1.sign_in_date,round(sum(if(t2.dt is not null,1,0))/count(1),2)

from(
    select uid,min(date(in_time)) sign_in_date
    from tb_user_log
    group by uid
)t1
left join (
    select uid,date(in_time) dt from tb_user_log 
    union
    select uid,date(out_time) dt from tb_user_log       
)t2 
on t1.uid = t2.uid   and t2.dt = DATE_ADD(t1.sign_in_date,INTERVAL 1 DAY)
group by t1.sign_in_date
having t1.sign_in_date >= '2021-11-01'
order by  t1.sign_in_date