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

with t1 as
(select 
    tag,
    sum(if_retweet) as day_retweet,
    substr(start_time,1,10) as day_time,
    sum(if_like) as day_like
from tb_user_video_log u
join tb_video_info v on u.video_id = v.video_id
group by tag,substr(start_time,1,10)),
t2 as
(select 
    tag,
    day_time,
    sum(day_like) over(partition by tag order by day_time rows between 6 preceding and current row) as sum_like_cnt_7d,
    max(day_retweet) over(partition by tag order by day_time rows between 6 preceding and current row) as max_retweet_cnt_7d
from t1)
select * from t2
where day_time in ('2021-10-01','2021-10-02','2021-10-03')
order by tag desc