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

select
    a.video_id,
    round(
        sum(
            if ((a.end_time - a.start_time) >= b.duration, 1, 0)
        ) / count(1),
        3
    ) avg_comp_play_rate
from
    tb_user_video_log a
    left  join tb_video_info b on a.video_id = b.video_id
where
    year (a.end_time) = 2021
group by
    a.video_id
order by avg_comp_play_rate desc

注意：b表不是主表很可能缺失，过滤条件必须优先想到用主表字段