SELECT a.video_id, 
       round(sum(if((end_time-start_time-b.duration) >=0,1,0))/count(a.video_id),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(start_time) = 2021
GROUP BY a.video_id
order by avg_comp_play_rate desc