题解 | 各个视频的平均完播率

with a as(
    select
        l.video_id as video_id,
        timestampdiff(second,start_time,end_time) as watch_time,
        duration
    from tb_user_video_log l
        join tb_video_info i on l.video_id=i.video_id
    where year(start_time)=2021),
b as (
    select
        video_id,
        case
            when watch_time >= duration
            then 1
        else 0
        end watch_all
    from a
)
select
    video_id,
    round(sum(watch_all)/count(*),3) as avg_comp_play_rate
from b
group by video_id
order by avg_comp_play_rate desc
;