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

select
    video_id
    , round((100 * rates + 5*likes + 3*comments + 2*retweets) / (no_play+1), 0) as hot_index
from (
    select
    t1.video_id
    , t2.release_time
    , max(t1.end_time) as max_date
    , sum(if(timestampdiff(second, t1.start_time, t1.end_time) >= t2.duration, 1, 0)) / count(t1.video_id) as rates
    , sum(t1.if_like) as likes
    , count(t1.comment_id) as comments
    , sum(t1.if_retweet) as retweets
    , datediff((select max(end_time) from tb_user_video_log), max(t1.end_time)) as no_play
    from tb_user_video_log t1 join tb_video_info t2 on t1.video_id = t2.video_id
    where t2.release_time >= date_sub((select max(end_time) from tb_user_video_log), interval 29 day)
    -- 检查视频是否在最近一个月发布，而不是比较 release_time 和 max(end_time)的时间差
    group by t1.video_id
    ) a
order by hot_index desc
limit 3