啊又是一道废掉脑细胞的题，总算做出来了

• 首先先筛选出时间在9.25到10.3的行，然后group by tag和date（start_time）来计算每天的总点赞量和总转发量，再按tag降序，日期升序排好表格，形成表格t1
• 重点来了，在表格t1的基础上利用**窗口函数（rows n preceding）**来计算近七天的总点赞数和最大转发量，形成表格t2
• 最后在表格t2的基础上筛选出时间在10.1到10.3号之间的，按照要求排序即可

select *
from
(select 
 tag,
 dt,
 sum(if_like_sum) over (partition by tag  order by dt rows 6 preceding) as sum_like_cnt_7d,
 max(if_retweet_sum) over (partition by tag  order by dt rows 6 preceding) as max_retweet_cnt_7d
 from(
select 
    tag,
    date(start_time) as dt,
    sum(if_like) as if_like_sum,
   sum(if_retweet) as if_retweet_sum
from tb_video_info tvi
join tb_user_video_log tuvl
on tvi.video_id=tuvl.video_id
where date(start_time) >='2021-09-25' and date(start_time) <='2021-10-03'
group by 1,2
order by 1 desc,2)t1)t2
where dt>='2021-10-01' and dt<='2021-10-03'
order by 1 desc ,2