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

-- 观看表自连接，求出同时在线的人数
with t as
(select 
    a.id,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)
-- 从同时在线表内取 最大同时在线的视频
select 
    cid,
    round(max(cnt),3) as max_peak_uv
from t
group by cid
order by max_peak_uv desc
limit 3;