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

-- 核心思路如下：
-- 1.贪心相同视频id的时间区间
-- 2.可转化为统计各cid中，该cid对应的start_time被多少个[start_time,end_time]包含
-- 3.group by a.id, a.cid, a.start_time
-- 注意：这里要引入id来确定唯一一行记录
select 
    cid,
    round(cast(max(cnt) as float), 3) as max_peak_uv
from (
    select 
        a.cid,
        sum(if(a.start_time between b.start_time and b.end_time, 1, 0)) as cnt
    from play_record_tb a 
    join play_record_tb b on a.cid=b.cid
    group by a.id, a.cid, a.start_time
) t
group by cid
order by max_peak_uv desc 
limit 3