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

with t as(
    select 
        tag,
        date_format(start_time,'%Y-%m-%d') as dt,
    sum(if_like) as like_cnt,
    sum(if_retweet) as retweet_cnt
    from tb_user_video_log a
    join tb_video_info b
        on a.video_id=b.video_id
    where date_format(start_time,'%Y-%m-%d') between '2021-09-25' and '2021-10-03'
    group by tag,dt
)

select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
from
    (select
        tag,
        dt,
        sum(like_cnt) over(partition by tag order by dt rows between 6 preceding and current row) as sum_like_cnt_7d,
        max(retweet_cnt) over (partition by tag order by dt rows between 6 preceding and current row) as max_retweet_cnt_7d
    from t
    group by tag,dt
    ) t1
where dt>='2021-10-01'
order by tag desc,dt