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

select
    *
from
    (
        select
            tag,
            # 不用date_format()用date()返回的数据类型不一致，再分组的时候报错，navicat忽略这个问题
            date (start_time)dt,
            sum(sum(if_like)) over (
                partition by
                    tag
                order by
                    date (start_time) range interval 6 day preceding
            ) sum_like_cnt_7d,
            max(sum(if_retweet)) over (
                partition by
                    tag
                order by
                    date (start_time) range interval 6 day preceding
            ) max_retweet_cnt_7d
        from
            tb_video_info i
            left join tb_user_video_log l on i.video_id = l.video_id
        group by
            tag,
            date (start_time)
    ) t
where
    dt between '2021-10-01' AND '2021-10-03'
order by
    tag desc,
    dt;