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

with t1 as(
    select
        dt,
        count(distinct uid) dau
    from(
        select 
            uid,
            date(in_time) dt
        from tb_user_log
        union
        select 
            uid,
            date(out_time) dt
        from tb_user_log
    ) a
    group by dt
),
t2 as(
    select
       date(first_dt) dt,
       count(uid) new_cnt
    from(
        select 
            uid, min(in_time) first_dt
        from tb_user_log
        group by uid
    ) b
    group by dt
)
select
    t1.dt,
    dau,
    round(ifnull(new_cnt/dau, 0), 2) uv_new_ratio
from t1 
left join t2 ON t1.dt = t2.dt
order by t1.dt