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

佬们请帮我看看这是错哪儿了？结果不对。只有最高热度的是对的。

select
video_id
,round((100*cmp_rt + 5*like_cnt + 3*comment_cnt + 2*retweet_cnt)*new_du) as hot_index
from
(
    select
    log.video_id
    ,avg(if(log.end_time -log.start_time >= info.duration, 1, 0)) as cmp_rt
    ,sum(if_like) as like_cnt
    ,sum(if(comment_id is not null, 1, 0)) as comment_cnt
    ,sum(if_retweet) as retweet_cnt
    ,1 / (1 + datediff('2021-10-03', max(log.end_time))) as new_du
    from tb_user_video_log log
    left join tb_video_info info
    on log.video_id = info.video_id
    where datediff('2021-10-03', info.release_time) <= 30  
    group by log.video_id
) a
order by hot_index desc
limit 3