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

select a.dt, round(count(b.uid) / count(a.uid), 2) as uv_rate
from (
select uid, min(date(in_time)) as dt
from tb_user_log
group by uid
) a
left join (
    select distinct uid, date(in_time) dt
    from tb_user_log
    union
    select distinct uid, date(out_time) dt
    from tb_user_log
) b
on a.uid = b.uid
and a.dt = date_sub(b.dt, interval 1 day)
where date_format(a.dt, '%Y-%m') = '2021-11'
group by a.dt
order by a.dt asc