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

select *
from (select 
    tag,
    dt,
    sum(like_cnt) over(partition by tag order by dt rows 6 preceding ) as sum_like_cnt_7d,
    max(retweet_cnt) over(partition by tag order by dt rows 6 preceding ) as max_retweet_cnt_7d
from (
    select 
        date(start_time) as dt,
        tag,
        sum(if_like) as like_cnt,
        sum(if_retweet) as retweet_cnt 
    from tb_user_video_log
    join tb_video_info using(video_id)
    where date(start_time) between '2021-09-25' and '2021-10-03'
    group by date(start_time), tag
) a 
) b 
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt