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

with a as(
select uid,dt,dense_rank() over(partition by uid order by dt) as rank1 from
(select uid,date(in_time) dt from tb_user_log
UNION
select uid,date(out_time) dt from tb_user_log t1
where date(out_time) not in(select date(in_time) from tb_user_log t2 where t1.uid=t2.uid)
)a1
    )
select dt,count(uid) as dau,round(sum(rank1=1)/count(uid),2) as uv_new_ratio
from a
group by dt