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

-- 核心思路如下：
-- 1.贪心相同视频id的时间区间
-- 2.可转化为统计各cid中，该cid对应的start_time被多少个[start_time,end_time]包含
-- 3.group by a.id, a.cid
-- 注意：这里要引入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 
    left join play_record_tb b on a.cid=b.cid
    group by a.id, a.cid
) t
group by cid
order by max_peak_uv desc 
limit 3

再来贡献一种写法：

select 
    cid,
    round(cast(max(cnt) as float), 3) as max_peak_uv
from (
    select 
        cid,
        sum(flag) over(partition by cid order by action_time) as cnt
    from (
        select 
            cid,
            start_time as action_time,
            1 as flag
        from play_record_tb a
        union all 
        select 
            cid,
            end_time as action_time,
            -1 as flag
        from play_record_tb b
    ) t
) tt
group by cid
order by max_peak_uv desc
limit 3