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

select * from (select tag ,dt ,sum(like_d)over(partition by tag order by dt rows between 6 preceding and current row) as sum_like_cnt_7d ,max(retweet_d)over(partition by tag order by dt rows between 6 preceding and current row) as max_retweet_cnt_7d from (select tag, date(start_time) dt, sum(if_like) like_d, sum(if_retweet) retweet_d from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id where year(a.start_time)=2021 group by 1,2) as t) t1 where dt between '2021-10-01' and '2021-10-03' order by tag desc,dt