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

with tmp as (
select
    uid,
    dt,
    rank() over(partition by uid order by dt) as rnk
from
    (
        select
            uid,
            date (in_time) as dt
        from
            tb_user_log
        union
        select
            uid,
            date (out_time) as dt
        from
            tb_user_log
    ) t
)
select dt, round(count(case when days_diff=1 then uid else null end)/count(distinct uid),2) as day1
from
(select t1.uid, t1.dt, datediff(t2.dt, t1.dt) as days_diff
from tmp as t1 left join tmp t2 on t1.uid = t2.uid
where year(t1.dt) = 2021 and month(t1.dt) = 11 and t1.rnk=1) t
group by dt
order by dt