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

select
t2.join_time as dt,
round(count(t1.uid = t2.uid) / count(*),2) as uv_left_rate
from
(select
uid,
min(date(in_time)) as join_time
from
tb_user_log
group by uid
) t2 
#每个用户uid及首次进入系统的时间
left join
(select
uid,
date(in_time) as time
from tb_user_log
union
select
uid,
date(out_time) as time
from tb_user_log) t1
#每个用户活跃的时间
on t2.uid = t1.uid
and date_add(t2.join_time,interval 1 day) = t1.time
group by t2.join_time
having year(dt) = 2021 and month(dt) = 11

也是看了题解才知道怎么做，使用连接的特性，在连接时指定一个连接条件，即加入系统的后一天的数据才能连接