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

-- 统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。 
-- 3、筛选出题目要求的国庆头3天对应的数据
select * 
from (
-- 2、计算每类视频每天对应的近7天的点赞总量以及最大转发量
select 
tag 
,dt 
,sum(like_cnt) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d 
,max(retweet_cnt) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
from (
-- 1、先获取每类视频每天的点赞量和转发量
select 
tag
,left(start_time,10) as dt 
,sum(if_like) as like_cnt 
,sum(if_retweet) as retweet_cnt 
from tb_user_video_log a 
left join tb_video_info b 
on a.video_id = b.video_id 
group by tag,dt ) c ) d 
where dt between '2021-10-01' and '2021-10-03' 
order by tag desc ,dt