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

select tag,dt,num1 sum_like_cnt_7d,num2 max_retweet_cnt_7d
from(
select tag,date(start_time) dt,sum(if_like),sum(if_retweet),sum(sum(if_like))over(partition by tag order by date(start_time) rows 6 preceding) num1,max(sum(if_retweet)) over(partition by tag order by date(start_time) rows 6 preceding) as num2
from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id
group by tag,dt
order by tag desc,dt) t
where dt between '2021-10-1' and '2021-10-3'