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

WITH
    overlap_status AS (
        SELECT
            t1.id,
            t1.cid,
            t1.start_time,
            t1.end_time,
            COUNT(t2.id) AS overlap_count
        FROM
            play_record_tb t1
            LEFT JOIN play_record_tb t2 -- left join
			ON t1.cid = t2.cid 
            AND t1.id != t2.id
            AND t2.end_time >= t1.start_time
            AND t1.end_time >= t2.start_time
        GROUP BY
            t1.id,
            t1.cid,
            t1.start_time,
            t1.end_time
    )
SELECT
    cid,
    round(
        cast(
            case
                when max(overlap_count) > 1 then max(overlap_count)
                when max(overlap_count) <= 1 then max(overlap_count) + 1
            end as float
        ),
        3
    ) as max_peak_uv
FROM
    overlap_status
group by
    cid
ORDER BY
    max_peak_uv desc
limit
    3;

比较通用的写法，画图能看出来t2.end_time >= t1.start_timeAND t1.end_time >= t2.start_time，这个条件包含了交集或者完全包含的情况，然后确定如果是大于1则计算了所有的记录，如果是小于等于1则还需要+1