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

WITH t1 AS (
    SELECT tag,
           DATE_FORMAT(start_time,'%Y-%m-%d') AS dt,
           SUM(if_like) AS like_cnt,
           SUM(if_retweet) AS retweet_cnt
    FROM tb_user_video_log tb1 
    LEFT JOIN tb_video_info tb2 ON tb1.video_id=tb2.video_id
    WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
    GROUP BY tag,dt
),
t2 AS (
    SELECT tag,dt,
           SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS 6 PRECEDING) AS sum_like_cnt_7d,
           MAX(retweet_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS 6 PRECEDING) AS max_retweet_cnt_7d
    FROM t1 
)
SELECT *
FROM t2 
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC,dt ASC