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

with tb_with_tag as (
    select
        tag,
        date(tb_user_video_log.start_time) as dt,
        if_like,
        if_retweet
    from 
        tb_user_video_log 
    left join 
        tb_video_info
    on 
        tb_user_video_log.video_id = tb_video_info.video_id
    where 
        date(tb_user_video_log.start_time) between '2021-09-25' and '2021-10-03'
),

tb_with_tag_1 as (
    select 
        tag,
        dt,
        sum(if_like) as sum_like,
        sum(if_retweet) as sum_retweet
    from 
        tb_with_tag
    group by 
        tag,
        dt
)

select
    *
from (
    select
        tag,
        dt,
        sum(sum_like) over (partition by tag order by dt desc rows between current row and 6 following) as sum_like_cnt_7d,
        max(sum_retweet) over (partition by tag order by dt desc rows between current row and 6 following) as max_retween_cnt_7d
    from    
        tb_with_tag_1
    group by 
        tag,
        dt
    order by 
        tag desc,
        dt
) as t1
where 
    dt between '2021-10-01' and '2021-10-03'