题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
SELECT
video_id,
ROUND((100 * finish_rate + 5 * like_num + 3 * comment_num + 2 * retweet_num) / (none_view_date + 1), 0) AS hot_index
FROM(
SELECT
video_id,
COUNT(IF(TIME_TO_SEC(TIMEDIFF(end_time,start_time)) >= duration, 1 , 0) = '1' OR NULL) /COUNT(IF(TIME_TO_SEC(TIMEDIFF(end_time,start_time)) >= duration, 1 , 0)) AS finish_rate,
COUNT(if_like = '1' OR NULL) AS like_num,
COUNT(comment_id IS NOT NULL OR NULL) AS comment_num,
COUNT(if_retweet = '1' OR NULL) AS retweet_num,
DATEDIFF((SELECT DATE(MAX(end_time)) FROM tb_user_video_log), DATE(MAX(end_time))) AS none_view_date
FROM
tb_user_video_log AS u
INNER JOIN
tb_video_info AS v
USING(video_id)
WHERE
release_time BETWEEN (SELECT DATE_SUB(DATE(MAX(end_time)), INTERVAL 29 DAY) FROM tb_user_video_log)
AND (SELECT DATE(MAX(end_time)) FROM tb_user_video_log)
GROUP BY video_id) AS a
ORDER BY hot_index DESC
LIMIT 3
