SELECT video_id,ROUND(AVG(IF( TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0 )),3) as avg_comp_play_rate FROM tb_user_video_log JOIN tb_video_info USING(video_id) WHERE YEAR(start_time)=2021 GROUP BY video_id ORDER BY avg_comp_play_rate DESC;