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

with t2 as (
    select uid, dt, min(dt) over(partition by uid) first_time
    from (
        select uid, date(in_time) dt
        from  tb_user_log
        union
        select uid, date(out_time) dt
        from  tb_user_log) t1)

select dt, count(*) dau,
    round(sum(if (dt = first_time, 1,0))/count(*),2) uv_new_ration
from t2 
group by dt