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

select
    dt,
    count(uid) as dau,
    round(avg(newd=dt),2) as uv_new_ratio
from (
    select uid,date(in_time) as dt from tb_user_log
    union
    select uid,date(out_time) as dt from tb_user_log
    ) as t1 
join (select uid,min(date(in_time)) as newd from tb_user_log group by uid ) as t2 using(uid)
group by dt
order by dt

avg(newd=dt) 等同于avg(if(newd=dt,1,0))