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

select in_time,
count(uid) as dau,
round(count(if(new_record=1,uid,null))/count(uid),2) as uv_new_ratio
from 
(select uid,in_time,
if(min(in_time)over(partition by uid)=in_time,1,0) as new_record
from
(select uid,date(in_time) in_time from tb_user_log
union
select uid,date(out_time) in_time from tb_user_log
order by uid)t)t2
group by in_time
order by 1