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

with
    t1 as (
        select
            b.tag tag,
            substr(a.start_time, 1, 10) start_time,
            sum(a.if_like) likes,
            sum(a.if_retweet) retweet
        from
            tb_user_video_log a
            left join tb_video_info b 
            on a.video_id =b.video_id
        group by
            b.tag,
            substr(a.start_time, 1, 10)
    )  
select *
from (
select
    c.tag,
    substr(c.start_time, 1, 10) dt,
#    c.likes, c.retweet,
    sum(c.likes) over (
        partition by
            c.tag
        order by substr(c.start_time, 1, 10) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) sum_like_cnt_7d,
    max(c.retweet) over (
        partition by
            c.tag
        order by
            substr(c.start_time, 1, 10) ROWS BETWEEN 6 PRECEDING
            AND CURRENT ROW
    ) max_retweet_cnt_7d
from
    (select * from t1  ) c
    order by c.tag desc,dt asc
)d 
where
    substr(d.dt, 1, 10) >= '2021-10-01'
    and substr(d.dt, 1, 10) <= '2021-10-03'