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

with a as(
    select
        tvi.tag,
        date(tuvl.start_time) as dt,
        tuvl.if_like,
        tuvl.if_retweet
    from 
        tb_user_video_log as tuvl
        left join tb_video_info as tvi on tuvl.video_id = tvi.video_id
    where
        date(tuvl.start_time) between '2021-09-25' and '2021-10-03'
),
b as(
    select
        tag,
        dt,
        sum(if_like) as day_like,
        sum(if_retweet) as day_retweet
    from 
        a
    group by 
        tag,dt
),
c as (
    select
        tag,dt,
        sum(day_like)over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
        max(day_retweet)over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
    from 
        b
),
d as(
    select
        *
    from 
        c 
    where
        dt between '2021-10-01' and '2021-10-03'
    order by 
        tag desc,
        dt asc
)
select * from d