select a.video_id,round(n/cnt,3) as avg_comp_play_rate from (select t1.video_id ,sum((case when (end_time-start_time)>=duration then 1 else 0 end)) as n
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) a
join 
(select  video_id,count(*) as cnt  from tb_user_video_log
where year(start_time)=2021 
group by video_id) b
on a.video_id=b.video_id
order by avg_comp_play_rate desc