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

with t1 as (
    select tag,date(start_time) df,
    sum(sum(if_like))over(partition by tag rows 6 preceding) sum_like_cnt_7d,
    max(sum(if_retweet))over(partition by tag rows 6 preceding) max_retweet_cnt_7d
    from tb_user_video_log tl left join tb_video_info ti on tl.video_id = ti.video_id
    group by tag,date(start_time)
)
select tag,df,sum_like_cnt_7d,max_retweet_cnt_7d from t1
where df between '2021-10-01' and '2021-10-03'
order by tag desc