题解 | 输出播放量最高的视频

with event_vedio as 
(
    select
        cid,
        start_time as event_time,
        1 as event_type
    from play_record_tb
    union all
    select
        cid,
        end_time as event_time,
        -1 as event_type
    from play_record_tb
),
concorrent as
(
    select
        cid,
        event_time,
        sum(event_type) over(partition by cid order by event_time,event_type) as counts
    from event_vedio
),
vedio_peak as 
(
    select
        cid,
        max(counts) as max_peak_uv
    from concorrent
    group by cid
)

select
    cid,
    round(max_peak_uv,3)
from vedio_peak
order by max_peak_uv desc
limit 3;

这里的播放量指的是并发播放峰值，某个时刻观看人数的峰值，相当于把时间段拆成时间点，把连续行为拆成离散事件，再通过累计求和还原实时人数，最后取最大值。