with t1 as 
(select tag,date(start_time) dt,sum(if_like) like_sum, sum(if_retweet) retweet_sum
 from tb_user_video_log log left join tb_video_info info
 on log.video_id=info.video_id
group by tag, date(start_time)),
t2 as(
select tag, dt, 
sum(like_sum) over (partition by tag order by dt rows 6 preceding) like_cnt,
max(retweet_sum) over (partition by tag order by dt rows 6 preceding) retweet_cnt
from t1)
select tag, dt, like_cnt, retweet_cnt
from t2
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt asc

思路：

  1. 按天聚合，计算sum(if_like),sum(if_retweet)
  2. 利用窗口函数计算一周内的点赞总和和最大转发量
  3. 筛选日期