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

select * from (
select c.tag,
       c.dt,
       sum(c.like_cnt) over(partition by c.tag order by c.dt rows 6 preceding) as sum_like_cnt_7d,
       max(retweet_cnt) over(partition by c.tag order by c.dt rows 6 preceding) as max_retweet_cnt_7d
from
(select tag,
       date_format(a.start_time,'%Y-%m-%d') as dt,
       sum(a.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
where date_format(a.start_time,'%Y-%m-%d') between '2021-09-25' and '2021-10-03'
group by b.tag,dt) c) t
where t.dt between '2021-10-01' and '2021-10-03'
order by t.tag desc,t.dt asc