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

select
    video_id,
    round((100*完播率+5*点赞数+3*评论数+2*转发数)*(1/(1+最近无播放天数)),0) as hot_index
from
    (   
        select 
            tb_user_video_log.video_id video_id
            ,AVG(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as 完播率
            ,sum(if_like) as 点赞数
            ,count(comment_id) as 评论数
            ,sum(if_retweet) as 转发数
            ,datediff((select max(end_time) from tb_user_video_log),max(end_time)) as  最近无播放天数
        from tb_user_video_log
        inner join tb_video_info
        on tb_user_video_log.video_id = tb_video_info.video_id
        where datediff((select date(max(end_time)) from tb_user_video_log),release_time)<=29
        group by tb_user_video_log.video_id
    ) as mid_t
order by  
    hot_index desc 
limit 3;