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

with
    a as (
        select
            distinct uid,
            date(in_time) as dt
        from
            tb_user_log
        union
        select
            distinct uid,
            date(out_time) as dt
        from
            tb_user_log
    ),
    b as (
        select
            uid,
            min(date(in_time)) as min_dt
        from
            tb_user_log
        group by
            1
    )
select
    min_dt,
    round(count(distinct a.uid) / count(distinct b.uid), 2)
from
    b
left join a on a.uid = b.uid
    and datediff(a.dt,min_dt) = 1
where min_dt >= '2021-11-01'
group by
    1
order by
    1;