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

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