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

select dt, round(avg(if_re is not null), 2) uv_left_rate
from (
    # 然后将新用户登录记录表与tb_user_log按特定条件连接
    select t2.uid, date(f_in) dt, min(in_time) if_re    # 其中如果if_re不为空，则是次日留存了的新用户
    from (
        # 先查询2021年11月每天的新用户在第一天登录的记录
        select *
        from (
            select uid, min(in_time) f_in
            from tb_user_log
            group by uid
        ) t1
        where date_format(f_in, '%Y%m') = '202111'
    ) t2
    left join tb_user_log t
    on t2.uid = t.uid and (datediff(date(t.in_time), date(t2.f_in))=1 or datediff(date(t.out_time), date(f_in))=1)
    group by t2.uid, f_in
) t3
group by dt
order by dt;