select
    video_id,
    round(sum(ifcomp)/count(distinct uid),3) avg_comp_play_rate
from(
    select
        v.video_id,
        v.uid,
        if(timestampdiff(second,v.start_time,v.end_time)>=i.duration,1,0) ifcomp
    from 
        tb_user_video_log v left join tb_video_info i 
        using(video_id)
    where left(v.start_time,4)='2021'
    ) t
group by 
    video_id
order by 
    avg_comp_play_rate desc