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

with
    max_end_time as (
        select
            max(end_time) as MT
        from
            tb_user_video_log
    ),
    recent_video as (
        select
            video_id,
            author,
            tag,
            duration,
            release_time
        from
            tb_video_info
        where
            release_time between (
                select
                    date_sub(MT, interval 29 day)
                from
                    max_end_time
            ) and (
                select
                    MT
                from
                    max_end_time
            )
    ),
    video_stats as (
        select
            rv.video_id,
            count(distinct t.id) as total_plays,
            sum(
                case
                    when TIMESTAMPDIFF(second, t.start_time, t.end_time) >= duration then 1
                    else 0
                end
            ) as complete_plays,
            sum(t.if_like) as likes,
            count(distinct t.comment_id) as comments,
            sum(t.if_retweet) as retweets,
            max(t.end_time) as last_play_time
        from
            recent_video rv
            left join tb_user_video_log t on rv.video_id = t.video_id
            and t.end_time between (
                select
                    date_sub(MT, interval 29 day)
                from
                    max_end_time
            ) and (
                select
                    MT
                from
                    max_end_time
            )
        GROUP BY
            rv.video_id,
            rv.duration
    ),
    hotness_calc as (
        SELECT
            vs.video_id,
            vs.total_plays,
            vs.complete_plays,
            vs.likes,
            vs.comments,
            vs.retweets,
            vs.last_play_time,
            case
                when vs.total_plays = 0 then 0
                else vs.complete_plays / vs.total_plays
            end as completion_rate,
            case
                when vs.last_play_time IS NOT NULL THEN DATEDIFF(
                    (
                        SELECT
                            MT
                        FROM
                            max_end_time
                    ),
                    vs.last_play_time
                )
                ELSE DATEDIFF(
                    (
                        SELECT
                            MT
                        FROM
                            max_end_time
                    ),
                    (
                        SELECT
                            DATE_SUB(MT, INTERVAL 29 DAY)
                        FROM
                            max_end_time
                    )
                )
            END AS days_no_play
        from
            video_stats vs
    )
SELECT
    video_id,
    ROUND(
        (
            100 * completion_rate + 5 * likes + 3 * comments + 2 * retweets
        ) / (days_no_play + 1)
    ) AS hot_index
FROM
    hotness_calc
WHERE
    total_plays > 0
ORDER BY
    hot_index DESC
LIMIT
    3