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

with t as (select log.video_id, substring(log.start_time, 1, 10) as date,
log.if_like, log.if_retweet, info.tag from tb_user_video_log log
left join tb_video_info info on log.video_id = info.video_id),

t2 as (select tag, date as dt,
sum(if_like) as sum_like_cnt_byday,
sum(if_retweet) as sum_retweet_cnt_byday
from t
group by tag, date),

t3 as (select tag, dt, 
sum(sum_like_cnt_byday) over (partition by tag order by dt rows between 6 preceding and current row )
as sum_like_cnt_7d,
max(sum_retweet_cnt_byday)  over (partition by tag order by dt rows between 6 preceding and current row)
from t2)

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