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

select
video_id
,round((avg(if_comp) * 100 + sum(if_like) * 5 + sum(if_comment) * 3 + sum(if_retweet) * 2 ) * (1 / (1 + min(diff_time)))) hot_index
from
(
    select
    log.video_id video_id
    ,if(timestampdiff(second, start_time, end_time) >= duration, 1, 0) if_comp
    ,if_like
    ,if_retweet
    ,if(comment_id is null, 0, 1) if_comment
    ,datediff((select max(end_time) from tb_user_video_log), end_time) diff_time
    from tb_user_video_log log
    left join tb_video_info info
    on log.video_id = info.video_id
    where datediff((select max(end_time) from tb_user_video_log), release_time) < 30
) a
group by video_id
order by hot_index desc
limit 3