题解 | #每类视频近一个月的转发量/率#

SELECT
    tb_video_info.tag,
    SUM(if_retweet) AS retweet_cut,
    ROUND(AVG(if_retweet), 3) AS retweet_rate
FROM
    (
        SELECT
            video_id,
            if_retweet
        FROM
            tb_user_video_log
        WHERE
            TIMESTAMPDIFF (
                DAY,
                DATE (tb_user_video_log.start_time),
                DATE (
                    (
                        SELECT
                            MAX(start_time)
                        FROM
                            tb_user_video_log
                    )
                )
            ) < 30
    ) AS tb_user_video_log
    JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id
GROUP BY
    tb_video_info.tag
ORDER BY retweet_rate DESC