题解 | #国庆期间每类视频点赞量和转发量#

with t1 as (select tag,date(start_time) as dt,sum(sum(if_like)) over(partition by tag order by date(start_time) rows between 6 preceding and current row) as like_cnt,max(sum(if_retweet)) over(partition by tag order by date(start_time) rows between 6 preceding and current row ) as retweet_cnt from tb_user_video_log join tb_video_info using(video_id) where date(start_time) between date('2021-09-25') and date('2021-10-03') group by tag,dt)
select tag,dt,like_cnt as sum_like_cnt_7d,retweet_cnt as max_retweet_cnt_7d from t1 where dt between date('2021-10-01') and date('2021-10-03') order by tag desc,dt