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

select t1.first_date ,
round(count(distinct t2.uid)/count(distinct t1.uid),2) as uv_left_rate
from (
    select uid, min(date(in_time)) as first_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.first_date,interval 1 day)
where substr(t1.first_date,1,7)='2021-11'
group by t1.first_date
order by t1.first_date

1）查新增用户的留存率，要先求first_date再联立

2）in_time和out_time都算活跃，要通过union得到（去重的）合并日期