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

select b.tag
,b.dt
,近一周总点赞量
,一周内最大单天转发量
from
(
    select a.tag
,a.dt
,sum(a.like) over(partition by a.tag order by a.dt rows 6 preceding ) 近一周总点赞量
,max(a.retweet) over(partition by a.tag order by a.dt rows 6 preceding ) 一周内最大单天转发量
from
(
    select tag
,substring(start_time,1,10) dt
,sum(if_like) 'like'
,sum(if_retweet) retweet
from tb_video_info v
left join tb_user_video_log u
on u.video_id = v.video_id
group by 1,2
)a
)b
where b.dt between '2021-10-01' and '2021-10-03'
group by 1,2
order by 1 desc,2 asc