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

select *
from
(select tag, dt,
    SUM(add_likes) OVER (
      PARTITION BY tag 
      ORDER BY dt DESC 
      ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING  -- 哥们following不能简写 ✅
    ) AS sum_like_cnt_7d,
    MAX(add_retweets) OVER (
      PARTITION BY tag 
      ORDER BY dt  
        rows 6 preceding  -- pedding可以 ✅
    ) AS max_retweet_cnt_7d
from (select tag,
     date(start_time) dt,
     sum(if_like) add_likes,
     sum(if_retweet) add_retweets
     from tb_user_video_log t1
     join tb_video_info t2
     on t1.video_id=t2.video_id
     group by tag, dt
     ) a
) b
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt