LIMIT6,3不行 | #国庆期间每类视频点赞量和转发量#

WITH a AS (
SELECT
    tag,
    DATE_FORMAT(start_time, '%Y-%m-%d' ) AS dt,
    COUNT(CASE WHEN if_like = 1 THEN l.id END) AS like_cnt,
    COUNT(CASE WHEN if_retweet = 1 THEN l.id END) AS retweet_cnt
FROM
    tb_user_video_log l
JOIN
    tb_video_info USING(video_id)
WHERE
    DATE_FORMAT(start_time, '%Y-%m-%d') BETWEEN DATE_SUB('2021-10-01', INTERVAL 6 DAY) AND '2021-10-03'
GROUP BY
    tag, dt)

, b as (SELECT
    tag,
    dt,
    SUM(like_cnt) OVER (PARTITION BY tag ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sum_like_cnt_7d,
    MAX(retweet_cnt) OVER (PARTITION BY tag ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS max_retweet_cnt_7d
FROM
    a
GROUP BY
    tag, dt
ORDER BY
    tag DESC, dt )

SELECT * FROM b WHERE dt between '2021-10-01' AND '2021-10-03'

#2021, between 10-01 and 10-03, group by tag, date, 
# SUM(if_like) OVER (PARTITION BY tag ORDER BY date) 
# MAX(if_retweet) OVER (PARTITION BY)一周内最大单天转发量