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

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