WITH t1 AS (
    SELECT
    DATE(MAX(start_time)) AS max_date,
    DATE_ADD(DATE(MAX(start_time)), INTERVAL -29 DAY) AS min_date
    FROM tb_user_video_log
),
t2 AS (
    SELECT tu.*,
           tv.tag,
           t1.*
    FROM tb_user_video_log tu
        JOIN t1
    LEFT JOIN tb_video_info tv
    ON tu.video_id = tv.video_id
    WHERE DATE(start_time) BETWEEN min_date AND max_date
),
t3 AS (
    SELECT tag,
           sum(if_retweet) AS retweet_cnt,
           count(tag) AS total
    FROM t2
    group by tag
),
t4 AS (
    SELECT tag,
           retweet_cnt,
           ROUND(retweet_cnt / total, 3) AS retweet_rate
    FROM t3
)
SELECT * FROM t4
ORDER BY retweet_rate DESC;

# 关键还是在于近30天的规则，是29。