最容易理解的，不用子查询，直接join

• 点赞数：sum(if_like)
• 评论数：count(comment_id)
• 转发数：sum(if_retweet)
• 因为最近播放日期以end_time-结束观看时间为准，所以需要在加一张表t3:(select max(end_time) as cur_time from tb_user_video_log)
• 最近无播放天数：datediff(cur_time,max(end_time)）
• 近一个月发布：having(datediff(cur_time,release_time)<30)

然后把组合起来就可以了

select
 	t2.video_id,
	round((100*avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0))+5*sum(if_like)+3*count(comment_id)+2*sum(if_retweet))/(datediff(cur_time,max(end_time))+1),0) as hot_index
from tb_user_video_log as t1 
    join tb_video_info as t2 using(video_id)
    join (select max(end_time) as cur_time from tb_user_video_log) as t3 on 1
group by 1,release_time,cur_time 
    having(datediff(cur_time,release_time)<30) 
order by 2 desc
limit 3