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

select *
from(
select tag
,date(start_time) dt
,sum(sum(if_like)) over(partition by tag order by date(start_time) rows 6 preceding) sum_like_cnt_7d
,max(sum(if_retweet)) over(partition by tag order by date(start_time) rows 6 preceding) max_retweet_cnt_7d
from tb_user_video_log join tb_video_info using(video_id)
where date(start_time) between '2021-09-25' and '2021-10-03'
group by tag,dt
) as t1
where t1.dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt asc;