• 视频完播率：avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0))
    • 点赞数：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