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

)
select * from a