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

-- 每天 日活数 新用户 占比
select dt,count(distinct uid) dau,
round(count(distinct case when min_date = dt then uid else null end)/count(distinct uid),2) uv_new_ratio
from 
(select uid,date(in_time) dt, min(date(in_time)) over (partition by uid) min_date
from tb_user_log
union all 
select uid,date(out_time) dt, min(date(in_time)) over (partition by uid) min_date
from tb_user_log) u 
group by dt