题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
SELECT
video_id,
round(( 100 * b_w + 5 * l_w + 3 * c_w + 2 * r_w ) / ( 1+f ), 0 ) hot_index
FROM
(
SELECT
t1.video_id,
avg( CASE WHEN timestampdiff( SECOND, start_time, end_time ) >= duration THEN 1 ELSE 0 END ) b_w,
sum( if_like ) l_w,
sum( CASE WHEN comment_id IS NOT NULL THEN 1 ELSE 0 END ) c_w,
sum( if_retweet ) r_w,
DATEDIFF((
SELECT
max( end_time )
FROM
tb_user_video_log
),
max( end_time )) f
FROM
tb_user_video_log t1
JOIN tb_video_info t2 ON t1.video_id = t2.video_id
GROUP BY
t1.video_id
) t
ORDER BY
hot_index DESC
那就小心你的腰子咯
