题解 | #每天的日活数及新用户占比#

# 先计算每一天的活跃用户人数
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, count(t3.uid) as new_uid
    from t2
    left join t3
    on t2.dt = t3.dt
    group by t2.dt
)
select t2.dt, t2.total_people as dau, round(t4.new_uid/t2.total_people, 2) as uv_new_ratio
from t2
left join t4
on t2.dt = t4.dt
order by t2.dt;