SELECT video_id,hot_index FROM( SELECT video_id ,round((100*over_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(no_play+1),0) as hot_index ,row_number()over(order by (100*over_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(no_play+1) desc) as rn FROM( SELECT distinct a1.video_id ,avg(if(TIMESTAMPDIFF(...