select t1.video_id,round(sum((case when (end_time-start_time)>=duration then 1 else 0 end))/count(*),3) as avg_comp_play_rate from  tb_user_video_log t1
join tb_video_info t2 
on t1.video_id =t2.video_id 
where year(start_time)=2021 
group by t1.video_id
order by avg_comp_play_rate desc