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

SELECT tag, dt, sum_like_7, max_retweet_7 from ( select tag, dt, sum(sum_like)over(partition by tag order by dt rows between 6 preceding and current row) as sum_like_7, max(sum_retweet)over(partition by tag order by dt rows between 6 preceding and current row) as max_retweet_7 FROM ( select tag, date(start_time) as dt, sum(if_like) as sum_like, sum(if_retweet) as sum_retweet from tb_video_info v join tb_user_video_log uv using(video_id) where date(uv.start_time) between '2021-09-25' and '2021-10-03' group by tag, date(start_time) )t1)t2 where dt between '2021-10-01' and '2021-10-03' order by tag desc, dt