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

又是离成功只差一点，这次一开始只写出了FROM后子查询的内容，发现输出与答案不符合，想了好久发现WHERE子句要写在子查询外才能输出正确结果。

除此之外还要注意滑动窗口函数rows between的用法和日期范围筛选是between and而不是between()。

这个题很绕还需要多加思考复习。

SELECT tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
FROM(SELECT tag,dt,
    sum(like_cnt)over(partition by tag rows between 6 preceding and current row) sum_like_cnt_7d,
    max(retweet_cnt)over(partition by tag rows between 6 preceding and current row) max_retweet_cnt_7d
FROM (SELECT
     tag, date(start_time) dt,
     sum(CASE WHEN if_like=1 THEN 1 ELSE 0 END) like_cnt,
     sum(CASE WHEN if_retweet=1 THEN 1 ELSE 0 END) retweet_cnt
     FROM tb_user_video_log t1
     LEFT JOIN tb_video_info t2 ON t1.video_id=t2.video_id
     WHERE date(start_time) BETWEEN '2021-09-25' AND'2021-10-03'
     GROUP BY tag,date(start_time)) a
ORDER BY tag DESC,dt) b
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03';