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

with b as
(
    select
        tag
        ,date_format (start_time,'%Y-%m-%d') dt
        ,sum(sum(if_like))over(partition by tag order by date_format (start_time, '%Y-%m-%d') rows 6 preceding)
        ,max(sum(if_retweet))over(partition by tag order by date_format (start_time, '%Y-%m-%d') rows 6 preceding)
    from
        tb_user_video_log a
        left join tb_video_info b on a.video_id = b.video_id
    where
        year (start_time) = 2021
        and datediff (
            '2021-10-03',
            date_format (start_time, '%Y-%m-%d')
        ) < 9
    group by tag,dt
)
select *
from b
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt