with sum_temp as (
select tag,date_format(start_time,'%Y-%m-%d') as dt ,
sum( if_retweet) as retweet_cnt,
sum(if_like ) as  like_cnt
from tb_video_info tvi
left  join  tb_user_video_log tul on tvi.video_id  =tul.video_id 
group by tag,dt
),
window_temp as (
select tag,dt , 
sum(like_cnt) over(partition by tag order by dt rows between 6 preceding and current row ) 
as sum_like_cnt_7d,
max(retweet_cnt) over(partition by tag order by dt rows between 6 preceding and current row ) 
as max_retweet_cnt_7d
from sum_temp
)
select * from window_temp
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt ;