SELECT a.video_id, round(count(if(end_time - start_time >= duration, 1, null))/count(start_time ),3) as avg_comp_play_rate FROM tb_user_video_log a,tb_video_info b WHERE a.video_id = b.video_id and year(start_time) = 2021 GROUP BY a.video_id ORDER BY avg_comp_play_rate DESC;