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

select dt
        ,round(count(distinct act_uid) / count(distinct uid),2) AS uv_left_rate
from(
    select first_date AS dt
        ,new_users.uid AS uid
        ,case when tl.uid is not null then tl.uid else null end AS act_uid
    from
    (
        select uid
            ,date(min(in_time)) AS first_date
        from tb_user_log
        group by uid
        having date_format(min(in_time),'%Y-%m') = '2021-11'
    )new_users
    left join tb_user_log tl on new_users.uid = tl.uid and datediff(date(tl.out_time),date(new_users.first_date)) = 1
)t
group by dt
order by dt

思路：新用户表 left join 用户表 ON 活跃日-注册日=1 ==》新用户注册日期以及次日是否活跃

group by 日期