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

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