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

with a2 as (with a1 as (select distinct * from(
select tag,date(start_time) date
,sum(if_like)over(partition by tag,date(start_time)) like_cnt
,sum(if_retweet)over(partition by tag,date(start_time)) retweet_cnt
from tb_user_video_log t1 
left join tb_video_info t2
on t1.video_id = t2.video_id
where year(start_time) = '2021'
) a)
select tag,date
,sum(like_cnt)over(partition by tag order by date range interval 6 day preceding ) 	sum_like_cnt_7d
,max(retweet_cnt)over(partition by tag order by date range interval 6 day preceding ) max_retweet_cnt_7d
from a1 )
select tag,date,sum_like_cnt_7d,max_retweet_cnt_7d
from a2 
where date between '2021-10-01' and '2021-10-03'
order by tag desc, date