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

with t1 as(
    SELECT
        uid,
        in_time event_time
    from tb_user_log
    union all
    select
        uid,
        out_time event_time
    from tb_user_log
)
select first_open,
        round(count(distinct s2.uid)/nullif(count(distinct s1.uid),0),2) uv_left_rate
from (select uid,min(date(event_time)) first_open from t1 group by 1) s1
left join t1 s2
on s1.uid = s2.uid and datediff(s2.event_time,s1.first_open) >= 1
where date_format(first_open,'%y-%m') = '21-11'
group by 1
order by 1

eezz