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

select
    t.video_id,
    round(avg(t.over_play), 3) avg_comp_play_rate 
from(
        select
            l.video_id,
            if (end_time - start_time>= i.duration, 1, 0) over_play
        from
            tb_user_video_log l,
            tb_video_info i
        where
            l.video_id = i.video_id and
            year(start_time) = 2021 and
            year(end_time) = 2021
    ) t
group by t.video_id
order by avg_comp_play_rate desc;