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

# 先计算每一天的活跃用户人数
with t1 as (
    select uid, date_format(in_time, '%Y-%m-%d') as dt
    from tb_user_log
    union
    select uid, date_format(out_time, '%Y-%m-%d') as dt
    from tb_user_log
),
t2 as (
    select dt, count(uid) as total_people
    from t1
    group by dt
),
t3 as (
    # 每一个用户的首次登录时间
    select uid, min(dt) as dt
    from t1
    group by uid
),
t4 as (
    # 查找每一天的新用户
    select t2.dt, t3.uid as new_uid
    from t2
    left join t3
    on t2.dt = t3.dt
),
t5 as (
    select t4.dt, count(t1.uid) as again_active
    from t1
    right join t4
    on date_add(t4.dt, interval 1 day) = t1.dt and t4.new_uid = t1.uid
    group by t4.dt
),
t6 as (
    select dt, max(uv_left_rate) as uv_left_rate
    from (
        select dt, 0.00 as uv_left_rate
        from t4
        where new_uid is not null and dt between '2021-11-01' and '2021-11-30'
        union all
        (select a.dt, round(t5.again_active/a.total_active, 2) as uv_left_rate
        from t5
        right join (
            select dt, count(new_uid) as total_active
            from t4
            group by dt
            having count(new_uid) != 0
        ) as a
        on t5.dt = a.dt)
    ) as d
    group by dt
)
select *
from t6
where dt between '2021-11-01' and '2021-11-30'
order by dt;