1. 近一个月发布的视频；
2. 计算各个视频的热度；
3. 找出热度TOP3的。

WITH recent_vedio_info AS
(SELECT video_id,duration
FROM tb_video_info
WHERE release_time >(SELECT subdate(max(end_time),29)max_date FROM tb_user_video_log)
)
SELECT 
    video_id,round((100*sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(*)+5*sum(if_like)+3*count(comment_id)+2*sum(if_retweet))
                   /(datediff((SELECT max(end_time) FROM tb_user_video_log),max(end_time))+1))hot_index
FROM recent_vedio_info INNER JOIN tb_user_video_log USING(video_id)
GROUP BY video_id
ORDER BY hot_index DESC
LIMIT 3;