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

select
tag,
dt,
sum_like_cnt_7d,
max_retweet_cnt_7d
from
	(select
	tvi.tag,
	date_format(start_time, "%Y-%m-%d") as dt,
	sum(sum(tuvl.if_like)) over(partition by tvi.tag order by date_format(start_time, "%Y-%m-%d") asc rows 6 preceding) as sum_like_cnt_7d,
	max(sum(tuvl.if_retweet)) over(partition by tvi.tag order by date_format(start_time, "%Y-%m-%d") asc rows 6 preceding) as max_retweet_cnt_7d
	from
	tb_video_info as tvi
	inner join
	tb_user_video_log as tuvl
	on tvi.video_id=tuvl.video_id
	group by tvi.tag, dt) as t
where dt between "2021-10-01" and "2021-10-03"
order by tag desc, dt asc

就是这么简单！