题解 | #每篇文章同一时刻最大在看人数#

with a3 as(
with a2 as(
with a1 as (select artical_id,in_time dt,1 cnt from tb_user_log where artical_id != 0
union all
select artical_id,out_time dt,-1 cnt from tb_user_log
where artical_id != 0
order by dt,cnt desc)
select artical_id,cnt,row_number()over(partition by artical_id order by dt) ranking
from a1)
select artical_id,sum(cnt)over(partition by artical_id order by ranking) sum_cnt
from a2)
select artical_id,max(sum_cnt) max_uv
from a3
group by artical_id
order by max_uv desc;