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

with t1 as(
    select artical_id, in_time as dt, 1 as diff
    from tb_user_log
    union all
    select artical_id, out_time as dt, -1 as diff
    from tb_user_log
)


select artical_id, max_uv
from
(select artical_id, max(counts) as max_uv
from
(select artical_id, sum(diff)over(partition by artical_id order by dt rows between unbounded preceding and current row) as counts
from
(select artical_id, dt, diff
from t1
order by dt) as t3) as t4
where artical_id !=0
group by artical_id) as t5
order by max_uv desc