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

select dt ,count(distinct uid) as dau , round(sum(if(rank_dt = 1,1,0)) / count(distinct uid),2) as uv_new_ratio 
from (
select uid ,dt ,rank() over(partition by uid order by dt) as rank_dt 
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 ) t2 
group by dt 
order by dt