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

WITH tb_user_video_log_maxtime AS(
    SELECT 
        *,MAX(end_time) OVER() max_time
    FROM    
        tb_user_video_log 
)
SELECT 
    video_id,
    ROUND((100*vedio_complete_rate + 5 * like_cnt + 3 * comment_cnt + 
    2 * retweet_cnt) / (DATEDIFF(max_time,vidio_max_time) + 1),0) hot_index
FROM(
SELECT
    t2.video_id,
    SUM(IF(TIMESTAMPDIFF(SECOND,t1.start_time,t1.end_time) >= 
           t2.duration,1,0)) / COUNT(t1.start_time) vedio_complete_rate,
    SUM(t1.if_like) like_cnt,
    COUNT(t1.comment_id) comment_cnt,
    SUM(t1.if_retweet) retweet_cnt,
    MAX(t1.end_time) vidio_max_time,
    max_time,t2.release_time
FROM 
    tb_user_video_log_maxtime t1
JOIN 
    tb_video_info t2 
ON    t1.video_id = t2.video_id
GROUP BY t2.video_id,max_time,t2.release_time
)tmp
WHERE DATEDIFF(max_time,release_time) BETWEEN 0 AND 29
ORDER BY hot_index DESC
LIMIT 0,3;