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

 # 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
#     ) tb1
# group by 
#     cid
# order by 
#     max_peak_uv desc
# limit 
#     3
with
    start_end_tb as (
        select
            cid,
            start_time as dt,
            1 as num
        from
            play_record_tb
        union all
        select
            cid,
            end_time as dt,
            -1 as num
        from
            play_record_tb
    ),
    rank_tb as (
        select
            cid,
            sum(num) over (
                partition by
                    cid
                order by
                    dt,
                    num desc
            ) as cnt
        from
            start_end_tb
    )
select
    cid,
    max(cnt) as max_peak_uv
from 
    rank_tb
group by 
    cid
order by 
    max_peak_uv desc
limit 
    3

正经的区间最大值写法