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

SELECT
	video_id,
	ROUND((100 * SUM(IF(play_tm >= duration, 1, 0)) / COUNT(video_id) + 3 * COUNT(comment_id) + 5 * SUM(if_like) + 2 * SUM(if_retweet)) /(MIN(play_diff) + 1), 0) hot_index
FROM
	(
		SELECT
			a.*,
			b.duration,
			b.release_time,
			timestampdiff(second, start_time, end_time) play_tm,
			MAX(SUBSTR(end_time, 1, 10)) over() last_time,
			DATEDIFF(MAX(SUBSTR(end_time, 1, 10)) over(), SUBSTR(end_time, 1, 10)) play_diff
		FROM
			tb_user_video_log a
		LEFT JOIN tb_video_info b
		ON
			a.video_id = b.video_id
	)
	t1
WHERE
	play_diff <= 29
	AND datediff(last_time, substr(release_time,1,10)) <=29
GROUP BY
	video_id
ORDER BY
	hot_index DESC
limit 3