with basic as
(select distinct tag,t.video_id,date(start_time) as dt
from tb_user_video_log t
join tb_video_info ti on t.video_id = ti.video_id
where date(start_time) in ('20211001','20211002','20211003')),

day_cnt as
(select tag,date(start_time) dt,sum(if_retweet)  as retweet_cnt ,sum(if_like) as like_cnt
from tb_user_video_log t 
join tb_video_info ti on t.video_id = ti.video_id
group by tag,date(start_time))

select b.tag,b.dt,sum(like_cnt) as sum_like_cnt_7d,max(retweet_cnt) as max_retweet_cnt_7d
from basic b
left join day_cnt d on d.dt between date_sub(b.dt,interval 6 day) and b.dt
and b.tag = d.tag
group by b.tag,b.dt
order by b.tag desc,b.dt

三刷 还是不熟练，day_cnt d 计算每天的情况，basic b提取出10月头三天，主查询用left join on两个条件（d的日期在b的前六天~b内；两表tag一致）将day_cnt 和basic两表连接，再汇总计算