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

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 uid,min(dt) min_dt from t1
    group by uid
)
select dt,count(*) dau,round(sum(if(min_dt=dt,1,0))/count(*),2)uv_new
from t1
left join t2
on t1.uid=t2.uid
group by dt