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

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