问题：找出近一个月发布的视频中热度最高的top3视频。
注：
热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；
新鲜度=1/(最近无播放天数+1)；
当前配置的参数a,b,c,d分别为100、5、3、2。
最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。
结果中热度保留为整数，并按热度降序排序。

1.连接表
select * 
from tb_user_video_log
inner join tb_video_info
using(video_id)

2.视频完播率
sum(if(TIMESTAMPDIFF(second,start_time,end_time)-duration>=0,1,0))/count(start_time)*100

3.点赞数、评论数、转发数
sum(if_like)*5,count(comment_id)*3,sum(if_retweet)*2

select video_id,
                sum(if(TIMESTAMPDIFF(second,start_time,end_time)-duration>=0,1,0))/count(start_time)*100,
                sum(if_like)*5,count(comment_id)*3,sum(if_retweet)*2
from tb_user_video_log
inner join tb_video_info
using(video_id)
group by video_id

4.新鲜度    

select video_id,
                timestampdiff(day,date(max(end_time)),date((select max(end_time) from tb_user_video_log)))+1
from tb_user_video_log
inner join tb_video_info
using(video_id)
group by video_id
            
            
5.最近一个月发布的视频

select * from tb_video_info
where timestampdiff(day,release_time,date((select max(end_time) from tb_user_video_log)))<=29

6. 汇总
select video_id,
                round((sum(if(TIMESTAMPDIFF(second,start_time,end_time)-duration>=0,1,0))/count(start_time)*100 +
                sum(if_like)*5 + count(comment_id)*3 + sum(if_retweet)*2) /
                (timestampdiff(day,date(max(end_time)),date((select max(end_time) from tb_user_video_log)))+1),0) hot_index
from tb_user_video_log
inner join (
                        select * from tb_video_info
                        where timestampdiff(day,date(release_time),date((select max(end_time) from tb_user_video_log)))<=29
                    ) t1
using(video_id)
group by video_id
order by hot_index desc
limit 3