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

with t1 as (
    select uid,date(in_time) dt
    from tb_user_log
    union
    select uid,date(out_time) dt
    from tb_user_log
),
t2 as (
    select distinct dt,count(uid) dau
    from t1
    group by dt
),
t3 as (
    select uid,min(dt) frist_dt
    from t1
    group by uid
),
t4 as (
    select frist_dt,count(uid) new
    from t3
    group by frist_dt
)
select dt,dau,ifnull(round(new/dau,2),0)
from t2 left join t4
on t2.dt = t4.frist_dt