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

with t1 as 
(select uid,date(in_time)as dt,date(min(in_time)over(partition by uid)) as min_dt,
case when date(in_time)=date(min(in_time)over(partition by uid)) then 1 else 0 end as new
from tb_user_log
UNION
select uid,date(out_time)as dt,date(min(in_time)over(partition by uid)) as min_dt,
case when date(out_time)=date(min(in_time)over(partition by uid)) then 1 else 0 end as new
from tb_user_log
order by uid, dt
)

select dt,count(*) as dau,round(sum(new)/count(*),2) as uv_new_ratio
from t1 group by dt