题解 | #近一个月发布的视频中热度最高的top3视频#

with tb1 as(
    select *, max(end_time)over() a, 
    datediff(max(end_time)over(),max(end_time)over(partition by video_id)) b,
    timestampdiff(second, start_time, end_time) c
    from tb_user_video_log)

select tb1.video_id, 
round((100*count(if(c>=duration,1,null))/count(tb1.video_id)+5*sum(if_like)+2*sum(if_retweet)+3*count(comment_id))/(max(b)+1),0) hot_index
from tb1 join tb_video_info tb2 
on tb1.video_id=tb2.video_id and datediff(a,release_time) between 0 and 29
group by tb1.video_id
order by hot_index desc
limit 3

#思路：所谓新鲜度、完播率、点赞转发评论数都是可以在video_id分组后一次性算出来的，所以优先考虑准备一张数据底表，然后做一次聚合计算即可。
#①上述计算需要的是最近播放日与每个video_id最近播放日之间的间隔天数，用两个窗口函数max做差；完播率需要的播放时长用timestampdiff计算，此时形成底表tb1；
#②选出1个月内发布的视频，用tb1与视频发布表join，加入连接条件发布时间与最近播放时间的差在0和29之间；
#③根据video_id分组，计算，即可。