select * from ( select tag, date_format(start_time,'%Y-%m-%d') dt, sum(sum(if_like)) over w sum_like_cnt_7d, max(sum(if_retweet)) over w max_retweet_cnt_7d from tb_user_video_log join tb_video_info using(video_id) group by tag, date_format(start_time,'%Y-%m-%d') window w as (partition by tag order by date_format(start_time,'%Y-%m-%d') ROWS 6 PRECEDING) ) tmp where dt >= '2021-10-01' and dt < '2021-10-04' order by tag desc, dt asc;