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

WITH a AS(
    SELECT artical_id, uid, in_time checktime, 1 uv
    FROM tb_user_log
    WHERE artical_id != 0
    UNION ALL
    SELECT artical_id, uid, out_time checktime, -1 uv
    FROM tb_user_log
    WHERE artical_id != 0
),
b AS(
    SELECT *,
    SUM(uv) OVER(PARTITION BY artical_id ORDER BY checktime,uv DESC) watch_num
    FROM a
),
c AS(
    SELECT artical_id,MAX(watch_num) max_uv
    FROM b
    GROUP BY artical_id
)
SELECT artical_id, max_uv
FROM c
ORDER BY max_uv DESC