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

# 窗口函数
with temp as (
    select tag, dt, 
           sum(sum_like) over(PARTITION BY tag ORDER BY dt RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) as sum_like_cnt_7d,
           max(sum_re) over(PARTITION BY tag ORDER BY dt RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) as max_retweet_cnt_7d
    from (    
        select tag, date(start_time) dt, 
               sum(if_like) sum_like, sum(if_retweet) sum_re
        from tb_user_video_log as t1 
        join tb_video_info as t2 on t1.video_id = t2.video_id
        where year(t1.start_time) = 2021
        group by tag, date(start_time)
    ) t3
)

select distinct *
from temp 
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt