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

select dt,
    count(distinct t1.uid) as dau,
    round(sum(if(t1.dt=t2.new_date,1,0))/count(distinct t1.uid),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
    order by dt asc
)t1 
left join
(
    select uid,min(date(in_time)) as new_date
    from tb_user_log
    group by uid
)t2
on t1.uid=t2.uid
group by dt
order by dt asc