with temp1 as (SELECT tag,
date(start_time) date,sum(if_like) if_like,sum(if_retweet) if_retweet
FROM tb_user_video_log a 
JOIN tb_video_info b using(video_id)
GROUP BY tag,date(start_time)
),
temp2 as(
SELECT tag,date dt, 
sum(if_like) over (PARTITION by tag ORDER BY date rows BETWEEN 6 preceding and current row) sum_like_cnt_7d,
MAX(if_retweet) over(PARTITION by tag ORDER BY date rows BETWEEN 6 preceding and current row) max_retweet_cnt_7d
from 
temp1
)

SELECT * from temp2 WHERE dt BETWEEN '2021-10-01' and '2021-10-03' ORDER BY tag desc,dt