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

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(1) as dau,
round(sum(if(dt=new_dt,1,0))/count(1),2) as uv_new_ratio
from t1
group by dt
order by dt

当日的新用户就是当日时间等于该用户的最小登陆时间