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

with t1 as (
    select a.video_id, date_format(a.start_time,'%Y-%m-%d') start_time, a.if_like, a.if_retweet, b.tag
    from tb_user_video_log a join tb_video_info b on a.video_id = b.video_id
--    where  date_format(a.start_time,'%Y-%m-%d') between date('2021-09-25') and date('2021-10-03')
), t2 as (
    select distinct tag, start_time, sum(if_like) over(partition by tag,start_time) if_like,
        sum(if_retweet) over(partition by tag,start_time) if_retweet
    from t1
), t3 as (
    select tag, start_time, 
        sum(if_like) over(partition by tag order by start_time rows between 6 preceding and current row) sum_like_cnt_7d,
        max(if_retweet) over(partition by tag order by start_time rows between 6 preceding and current row) max_retweet_cnt_7d
    from t2
), t4 as (
    select tag, start_time, sum_like_cnt_7d, max_retweet_cnt_7d
    from t3
    where start_time between date('2021-10-01') and date('2021-10-03')
    order by tag desc, start_time 
)
select * from t4