题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
一、思路
将问题分解为两个子问题来做的话，这道题相对来说还是比较简单的
（一）查出符合条件的计算热度所需要的各个字段值
（二）在（一）的基础上直接计算即可
二、详细步骤
（一）查出符合条件的计算热度所需要的各个字段值
1、找出近一个月发布的视频
这里看其他热门的回答基本上都是用MAX(start_time)减去30天得到期限，但从题目的意思来看的话，MAX(start_time)是2021-10-03 11:00:50，那么这个区间其实应该是 [2021-09-04 00:00:00----2021-10-03 24:00:00]，所以我作答的筛选的条件为
WHERE release_time BETWEEN date_sub(date_format((SELECT max(end_time) FROM tb_user_video_log), '%Y%m%d'), interval 29 day) AND date_add(date_format((SELECT max(end_time) FROM tb_user_video_log), '%Y%m%d'), interval 1 day)
2、找出计算热度所需要的字段
WITH t1 AS (SELECT tu.video_id , IF(timestampdiff(second, start_time, end_time)>=duration, 1, 0) if_complete , if_like , IF(comment_id IS NOT NULL, 1, 0) if_comment , if_retweet , timestampdiff(day, date_format(end_time, '%Y%m%d'), date_format((SELECT max(end_time) FROM tb_user_video_log), '%Y%m%d')) diff_day FROM tb_user_video_log tu JOIN tb_video_info tv ON tu.video_id = tv.video_id WHERE release_time BETWEEN date_sub(date_format((SELECT max(end_time) FROM tb_user_video_log), '%Y%m%d'), interval 29 day) AND date_add(date_format((SELECT max(end_time) FROM tb_user_video_log), '%Y%m%d'), interval 1 day))
（二）在（一）的基础上直接计算即可
WITH t1 AS (SELECT tu.video_id , IF(timestampdiff(second, start_time, end_time)>=duration, 1, 0) if_complete , if_like , IF(comment_id IS NOT NULL, 1, 0) if_comment , if_retweet , timestampdiff(day, date_format(end_time, '%Y%m%d'), date_format((SELECT max(end_time) FROM tb_user_video_log), '%Y%m%d')) diff_day FROM tb_user_video_log tu JOIN tb_video_info tv ON tu.video_id = tv.video_id WHERE release_time BETWEEN date_sub(date_format((SELECT max(end_time) FROM tb_user_video_log), '%Y%m%d'), interval 29 day) AND date_add(date_format((SELECT max(end_time) FROM tb_user_video_log), '%Y%m%d'), interval 1 day)) SELECT video_id , ROUND((100*SUM(if_complete)/COUNT(video_id) + 5*SUM(if_like) + 3*SUM(if_comment) + 2*SUM(if_retweet))/(MIN(diff_day)+1)) hot_index FROM t1 GROUP BY video_id ORDER BY hot_index DESC LIMIT 3
投递拼多多集团-PDD等公司10个岗位
