select video_id,round(sum(if(vt >= duration,1,0))/count(1),3) as avg_comp_play_rate
from (select t.video_id, timestampdiff(second, start_time, end_time) as vt, duration
      from tb_user_video_log tuvl
               join tb_video_info t on tuvl.video_id = t.video_id
      where year(end_time)=2021) a
group by video_id
order by avg_comp_play_rate desc;