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

with t1 as(
    select 
        distinct uid, date(in_time) dt, min(date(in_time))over(partition by uid) new_dt
    from 
        tb_user_log
    union 
    select 
        distinct uid, date(out_time) dt, min(date(in_time))over(partition by uid) new_dt
    from
        tb_user_log
)
select 
    dt, count(*) dau, round(sum(if(dt=new_dt,1,0))/ count(*), 2) uv_new_ratio
from 
    t1
group by 
    dt 
order by 
    dt asc;