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

SELECT
    video_id,
    ROUND(
        (
            100 * play_complete_rate + 5 * sum_like + 3 * sum_comment + 2 * sum_retweet
        ) / (1 + no_play_days),
        0
    ) AS hot_index
FROM
    (
        SELECT
            video_id,
            SUM(
                IF(
                    TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration,
                    1,
                    0
                )
            ) / COUNT(*) AS play_complete_rate,
            SUM(if_like) AS sum_like,
            SUM(IF(comment_id IS NULL, 0, 1)) AS sum_comment,
            SUM(if_retweet) AS sum_retweet,
            MIN(
                DATEDIFF(
                    (
                        SELECT
                            DATE(MAX(end_time))
                        FROM
                            tb_user_video_log
                    ),
                    DATE(end_time)
                )
            ) AS no_play_days
        FROM
            tb_video_info
            JOIN tb_user_video_log USING (video_id)
        WHERE
            DATE(release_time) -- 近一个月发布的
            BETWEEN (
                SELECT
                    DATE_SUB(DATE(MAX(end_time)), INTERVAL 29 DAY)
                FROM
                    tb_user_video_log
            ) -- 09-04 27天
            AND (
                SELECT
                    DATE(MAX(end_time))
                FROM
                    tb_user_video_log
            ) -- 10-03 3天
        GROUP BY
            video_id
    ) AS t1
ORDER BY
    hot_index DESC
LIMIT
    3