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

SELECT
    video_id,
    ROUND((AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0))*100 +
    SUM(if_like)*5 +
    COUNT(comment_id)*3 +
    SUM(if_retweet)*2) /
    (DATEDIFF(today,MAX(end_time))+1),0) hot_index
FROM(
    SELECT
        uvl.*,
        vi.duration,
        vi.release_time,
        MAX(end_time) OVER() today
    FROM
        tb_user_video_log uvl
    INNER JOIN
        tb_video_info vi
        ON uvl.video_id = vi.video_id
)t
WHERE
    DATEDIFF(today, release_time) <= 29
GROUP BY
    video_id,today
ORDER BY
    hot_index DESC
LIMIT
    3