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

SELECT *
FROM(
    SELECT 
        tag,
        DATE(start_time) dt,
        SUM(SUM(if_like))OVER(PARTITION BY tag ORDER BY DATE(start_time) ROWS 6 PRECEDING) sum_like_cnt_7d,
        MAX(SUM(if_retweet))OVER(PARTITION BY tag ORDER BY DATE(start_time)ROWS 6 PRECEDING) max_retweet_cnt_7d
    FROM tb_user_video_log u
    JOIN tb_video_info v ON u.video_id = v.video_id 
    WHERE DATEDIFF('2021-10-03', DATE(start_time)) >= 0
    GROUP BY tag, DATE(start_time)
) a
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC, dt