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

WITH
    daily_agg AS (
        SELECT
            b.tag,
            DATE_FORMAT(a.start_time, '%Y-%m-%d') AS dt,
            SUM(a.if_like) AS daily_like_cnt,
            SUM(a.if_retweet) AS daily_retweet_cnt
        FROM
            tb_user_video_log a
            JOIN tb_video_info b USING (video_id)
        WHERE
            DATEDIFF('2021-10-03', DATE(a.start_time)) < 9
        GROUP BY
            b.tag,
            DATE_FORMAT(a.start_time, '%Y-%m-%d')
    ),
    window_stats AS (
        SELECT
            tag,
            dt,
            SUM(daily_like_cnt) OVER (
                PARTITION BY
                    tag
                ORDER BY
                    dt ROWS BETWEEN 6 PRECEDING
                    AND CURRENT ROW
            ) AS sum_like_cnt_7d,
            MAX(daily_retweet_cnt) OVER (
                PARTITION BY
                    tag
                ORDER BY
                    dt ROWS BETWEEN 6 PRECEDING
                    AND CURRENT ROW
            ) AS max_retweet_cnt_7d
        FROM
            daily_agg
    )
SELECT
    *
FROM
    window_stats
WHERE
    dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY
    tag DESC,
    dt ASC;