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

-- 需求：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。
-- 拆解思路如下：
-- 1、使用left join语句将互动表与信息表中所需要的内容进行连接
-- 2、国庆前3天每天对应的近一周日期应该是  最早日期是9月24日，与10月3日之间相差9天。每个日期对应的近一周数据，可以使用窗口排序函数以及 rows n precedinng来进行计算 
select * 
from (
select 
tag 
,date(start_time) as dt 
,sum(sum(if_like)) over (partition by tag order by date(start_time) rows 6 preceding) as sum_like_cnt_7d 
,max(sum(if_retweet)) over (partition by tag order by date(start_time) rows 6 preceding) as max_retweet_cnt 
from tb_user_video_log t1 
left join tb_video_info t2 
on t1.video_id = t2.video_id 
where year(start_time) = 2021 
and datediff('2021-10-03',date(start_time)) <= 9 
group by tag ,dt 
order by tag desc ,dt ) t 
where dt between '2021-10-01' and '2021-10-03'