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

with t1 as (
select tag, date(start_time) dt, sum(if_like) like_cnt, sum(if_retweet) retweet_cnt
from tb_user_video_log a 
inner join tb_video_info b on a.video_id = b.video_id
group by tag, date(start_time)), t2 as (
select tag, dt, sum(like_cnt) over(partition by tag order by dt range between interval '6' day preceding and current row) sum_like_cnt_7d,
    max(retweet_cnt) over(partition by tag order by dt range between interval '6' day preceding and current row) max_retweet_cnt_7d
from t1)

select *
from t2 
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt