牛客大厂sql163

-- todo 1 将用户的进入时间单独拎出来，同时记为1；离开时间单独拎出来，同时记为-1，这样就聚合这两个表
with t1 as(
    select artical_id,
           in_time dt,
           1 num
    from tb_user_log
    where artical_id != 0
    union all
    select artical_id,
           out_time dt,
           -1 num
    from tb_user_log
    where artical_id != 0
)
-- todo 2 利用窗口函数对计数（1或者-1）求累计和
,t2 as(
    select artical_id,
           sum(num) over(partition by artical_id order by dt asc,num desc) as num_cnt
    from t1
)
-- todo 求最大的累积和
select artical_id,
       max(num_cnt) max_uv
from t2
group by artical_id
order by max_uv desc;