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

不应该是LEFT JOIN 是JOIN

计算完播次数时是>=duration

WITH t AS (SELECT
b.video_id,
COUNT(IF(TIMESTAMPDIFF(SECOND,b.start_time,b.end_time) >= a.duration ,1,NULL))/COUNT(*) AS finish_rate,
SUM(if_like) AS likes,
COUNT(comment_id) AS comments,
SUM(if_retweet) AS retweet,
1/(DATEDIFF((SELECT MAX(DATE(end_time)) FROM tb_user_video_log),MAX(end_time))+1) AS fresh
FROM tb_video_info a
JOIN tb_user_video_log b USING(video_id)
WHERE DATE(release_time) BETWEEN DATE_SUB((SELECT MAX(DATE(end_time)) FROM tb_user_video_log),INTERVAL 29 DAY) AND (SELECT MAX(DATE(end_time)) FROM tb_user_video_log)
GROUP BY b.video_id)

SELECT
video_id,
ROUND((100*finish_rate+5*likes+3*comments+2*retweet)*fresh) AS hot_index
FROM t
ORDER BY hot_index DESC
LIMIT 3;