题解 | #国庆期间每类视频点赞量和转发量#

SELECT * 
FROM (
    SELECT tag, dt,
        SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS sum_like_cnt_7d,
        MAX(retweet_cnt) OVER(PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS retweet_cnt_7d
    FROM (
        SELECT tag, 
            DATE(start_time) AS dt,
            SUM(IF(if_like = 1, 1, 0)) AS like_cnt,
            SUM(IF(if_retweet = 1, 1, 0)) AS retweet_cnt
        FROM tb_user_video_log
            JOIN tb_video_info
            USING(video_id)
        WHERE DATE(start_time) BETWEEN '2021-09-25' AND '2021-10-03' 
        GROUP BY tag, dt
    )cnt_table
)window_sum_t
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC, dt