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

SELECT
    t2.tag,
    t2.d dt,
    week_like_sum sum_like_cnt_7d,
    week_max_retweet max_retweet_cnt_7d
FROM
    (
        SELECT
            t1.tag,
            t1.d,
            SUM(if_like_sum) OVER (
                PARTITION BY
                    tag
                ORDER BY
                    d ROWS 6 PRECEDING
            ) week_like_sum,
            MAX(if_retweet_sum) OVER (
                PARTITION BY
                    tag
                ORDER BY
                    d ROWS 6 PRECEDING
            ) week_max_retweet
        FROM
            (
                SELECT
                    tvi.tag,
                    DATE(tuv.start_time) d,
                    SUM(tuv.if_like) if_like_sum,
                    SUM(tuv.if_retweet) if_retweet_sum
                FROM
                    tb_user_video_log tuv
                    JOIN tb_video_info tvi USING (video_id)
                GROUP BY
                    tvi.tag,
                    d
            ) t1
    ) t2
WHERE d BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC,d