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

select * from (
	select tag, dt,
	sum(like_total) over(partition by tag ORDER BY dt rows 6 preceding) as sum_like_cnt_7d,
	max(retweet_total) over(partition by tag ORDER BY dt rows 6 preceding) as max_retweet_cnt_7d
	from (
		select tag, sum(if_like) as like_total, sum(if_retweet) as retweet_total, date(start_time) as dt
		from tb_video_info a
		left join tb_user_video_log using(video_id)
		WHERE DATE(start_time) BETWEEN '2021-09-25' AND '2021-10-03'
		group by tag, date(start_time)
	) t
) t2
where dt >= '2021-10-01' and dt <= '2021-10-03'
group by tag, dt
order by tag desc, dt asc

逆天的题 坑一堆