with t1 as(
select video_id, timestampdiff(second,start_time,end_time) as take_time
from tb_user_video_log
where year(start_time) = 2021
)
select video_id,round(sum(if(take_time >= duration,1,0))/count(take_time),3)  as avg_comp_play_rate
from tb_video_info
join t1 using (video_id)
group by video_id
order by avg_comp_play_rate desc