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

select * from
(select 
tag,date_format(start_time,'%Y-%m-%d') as dt,
sum(sum(if_like)) over (partition by tag order by date_format(start_time,'%Y-%m-%d') rows 6 preceding) as sum_like_cnt_7d,
max(sum(if_retweet)) over (partition by tag order by date_format(start_time,'%Y-%m-%d') rows 6 preceding) as max_retweet_cnt_7d

from tb_user_video_log t1
right join tb_video_info t2 on t1.video_id = t2.video_id


group by tag,date_format(start_time,'%Y-%m-%d'))a
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt

二刷没做出来的点：

1）sum(sum(if_like)) 先算一天的，再算该天及往前6天累积的

2）where dt between '2021-10-01' and '2021-10-03'应该放在外层