关键点一：寻找最近的播放日期

    思路：每个视频的最近（最新的播放日期）播放日期中，筛选出最大的，就是所有视频中最近的播放日期。
     
select max(maxtime) over()
from (select
video_id,
max(end_time) as maxtime
from tb_user_video_log
group by video_id
)a

关键点二：近一个月发布的视频

    思路：发布日期和（所有播放记录中最新播放的日期=最近） 差值在29天内。
  
select video_id,
        #每个视频中最后播放时间整体作为一个窗口，选择最后的播放时间，就是播放日期
        datediff(max(maxtime) over(),maxtime) as noplayday,
        #最新日期和发布日志的时间差值
        datediff(max(maxtime) over(),maxreleasetime) as reaseday#只为了做近一个月内发布视频判断条件
        from  (select
            l.video_id,
            #每个视频最后播放时间
            max(end_time) as maxtime,
            #每个视频的发布时间
            max(release_time) as maxreleasetime
            from tb_user_video_log l 
            join tb_video_info i 
            on l.video_id = i.video_id
            group by l.video_id
            )a
        )b
        where reaseday<=29

关键点三：完播放率

    思路：（结束播放时间和开始播放时间的差值大于等于视频本身的长度的播放记录）和总播放记录的比值。
  
sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(start_time) as finishPlayRate

整体答案： 

select video_id,hot_index 
from
   (select video_id,
    round((100*finishPlayRate+5*likecnt+3*commentcnt+2*retcnt)*(1/(noplayday+1)),0) as hot_index
    from
        (select video_id,
        finishPlayRate,
        likecnt,
        commentcnt,
        retcnt,
        #每个视频中最后播放时间整体作为一个窗口，选择最后的播放时间，就是播放日期
        datediff(max(maxtime) over(),maxtime) as noplayday,
        datediff(max(maxtime) over(),maxreleasetime) as reaseday#只为了做近一个月内发布视频判断条件
        from  (select
            l.video_id,
            #完播放率
            sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(start_time) as finishPlayRate,
            #点赞数
            sum(if_like) as likecnt,
            #评论数
            sum(if(comment_id is not null,1,0)) as commentcnt,
            #转发数
            sum(if_retweet) as retcnt,
            #每个视频最后播放时间
            max(end_time) as maxtime,
            #每个视频的发布时间
            max(release_time) as maxreleasetime
            from tb_user_video_log l 
            join tb_video_info i 
            on l.video_id = i.video_id
            group by l.video_id
            )a
        )b
        where reaseday<=29
     )c
order by hot_index desc 
limit 3;