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

select tag, dt,sum_like_cnt_7d, max_retweet_cnt_7d
from(
select 
i.tag,
date(v.start_time) as dt,
sum(v.if_like) as likedt,
sum(v.if_retweet) as retwdt,
sum(sum(v.if_like)) over (partition by tag order by date(v.start_time) rows 6 preceding) as sum_like_cnt_7d,
max(sum(v.if_retweet))over (partition by tag order by date(v.start_time) rows 6 preceding) as max_retweet_cnt_7d
from tb_user_video_log v
left join tb_video_info i 
on v.video_id = i.video_id
where date(v.start_time) between "2021-9-25" and "2021-10-03"
group by tag,dt
)tt 
where dt between "2021-10-01" and "2021-10-03"
order by tag desc, dt asc