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

1. 注意join on中有两个条件
2. 注意理解题目第二天又活跃的意思，应该用in_time和on_time做比较

select t2.dt, round(count( t3.uid ) / count(t2.uid), 2)
from 
(
select uid, date(in_time) dt
from (
select uid, in_time, 
row_number() over(partition by uid order by in_time asc) r
from tb_user_log
) t1 
where t1.r = 1
) t2 
left join 
(select distinct uid, date(out_time) dt from tb_user_log ) t3
on t2.uid = t3.uid and date_add(t2.dt, interval 1 day) = t3.dt
where t2.dt between '2021-11-01' and '2021-11-30'
group by 1 
order by 1 asc