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

with tb as(
	select
		tag,
		date(start_time) dt,
		if_like,
		if_retweet
	from
		tb_user_video_log u
		join
		tb_video_info v
		on u.video_id = v.video_id
)
select
	tag,
	dt,
	sum(if_like) sum_like_cnt_7d,
	max(if_retweet) max_retweet_cnt_7d
from
(select 
	t1.tag tag,
	t1.dt dt,
	t2.dt dt2,
	sum(t2.if_like) if_like,
	sum(t2.if_retweet) if_retweet
from 
	(select distinct tag,dt from tb where dt between '2021-10-01' and '2021-10-03') t1
	join
	tb t2
	on
	t1.tag=t2.tag
	and 
	t2.dt between t1.dt - interval 6 day and t1.dt
group by
	tag,dt,dt2) t
group by
	tag,dt
order by
	tag desc, dt asc;