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

WITH t AS (
    SELECT
    tag,
    DATE(start_time) AS dt,
    SUM(if_like) sum_like_cnt,
    SUM(if_retweet) sum_retweet_cnt
    FROM tb_user_video_log
    JOIN tb_video_info USING(video_id)
    GROUP BY tag,dt
),

# SELECT
# tag,
# dt,
# SUM(sum_like_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS 6 PRECEDING) AS sum_like_cnt_7d,
# MAX(sum_retweet_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS 6 PRECEDING) AS max_retweet_cnt_7d
# FROM t
# GROUP BY tag,dt
# HAVING dt BETWEEN '2021-10-01' AND '2021-10-03';
# 这样是错的，窗口函数最好单独用

t2 AS (SELECT
tag,
dt,
SUM(sum_like_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS 6 PRECEDING) AS sum_like_cnt_7d,
MAX(sum_retweet_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS 6 PRECEDING) AS max_retweet_cnt_7d
FROM t)

SELECT
tag,
dt,
sum_like_cnt_7d,
max_retweet_cnt_7d
FROM t2
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC,dt