题解 | 临时表——可读性好

with new_user_t as (
    SELECT *
    from tb_user_log
    where (uid,in_time) in (
      select uid,min(in_time)
      from tb_user_log
      group by uid
        )
    and date_format(in_time,'%Y-%m')='2021-11'
), -- 每天新用户表
active_user_t as (
    select uid,in_time
    from tb_user_log
    where date_format(in_time,'%Y-%m')='2021-11'
    union
    select uid,out_time
    from tb_user_log
    where date_format(in_time,'%Y-%m')='2021-11'
) -- 每天活跃用户表
select date(b.in_time) dt
,round(count(distinct case when datediff(a.in_time,b.in_time)=1 then b.uid end)/count(distinct b.uid),2) 次日留存率
from active_user_t a
inner join new_user_t b
on a.uid=b.uid
group by date(b.in_time)
order by dt