select
    a.video_id,
    round((sum(if(end_time-start_time >= duration, 1, 0))/count(start_time)), 3) as 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(start_time) = 2021
group by 1
order by 2 desc;