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

SELECT video_id
     , ROUND((100 * SUM(CASE WHEN TIMESTAMPDIFF(SECOND,start_time,end_time) < duration THEN 0 ELSE 1 END) / COUNT(*) +
              5 * SUM(if_like) + 3 * COUNT(comment_id) + 2 * SUM(if_retweet)) /
             (DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log),MAX(DATE(end_time))) + 1)) AS hot_index
FROM tb_user_video_log JOIN tb_video_info USING (video_id)
WHERE DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log),release_time) <= 29
GROUP BY video_id
ORDER BY hot_index DESC
LIMIT 3;

SELECT video_id
     , SUM(CASE WHEN TIMESTAMPDIFF(SECOND,start_time,end_time) < duration THEN 0 ELSE 1 END) / COUNT(*) AS "完播率"
     , SUM(if_like)                                                 AS "点赞数"
     , COUNT(comment_id)                                              AS "评论数"
     , SUM(if_retweet)                                               AS "转发数"
     , DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log),MAX(DATE(end_time)))             AS "新鲜度"
FROM tb_user_video_log JOIN tb_video_info USING (video_id)
WHERE DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log),release_time) <= 29
GROUP BY tb_user_video_log.video_id；