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

SELECT
tag,
dt,
sum_like_cnt_7d,
max_retweet_cnt_7d

FROM(
    SELECT
    tag,
    dt,
    SUM(SUM(if_like)) OVER(PARTITION BY tag ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sum_like_cnt_7d,
    MAX(SUM(if_retweet)) OVER(PARTITION BY tag ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS max_retweet_cnt_7d

    FROM(
    SELECT
    tag,
    if_like,
    if_retweet,
    DATE_FORMAT(start_time, '%Y-%m-%d') AS dt

        FROM
        tb_user_video_log AS u
        INNER JOIN 
        tb_video_info AS v
            USING(video_id)) AS a

    GROUP BY tag, dt) AS b

WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'

ORDER BY tag DESC, dt