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

with t1 as(
    select *,
    min(date(in_time)) over(partition by uid) as first_in
    from tb_user_log
    # group by uid
),
t2 as(
    select uid, date(in_time) as dt, first_in from t1
    union
    select uid, date(out_time) as dt, first_in from t1
),
t3 as(
    select uid, dt,
    if(datediff(dt,first_in)=0,1,0) as is_new
    from t2
)
select dt,
count(uid) as dau,
round(sum(is_new)/count(uid),2) as uv_new_ratio
from t3
group by dt
order by dt asc