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

with
    t4 as (
        select
            tag,
            dt,
            sum(if_like) as like_cnt,
            sum(if_retweet) as retweet_cnt
        from
            (
                select
                    t1.video_id,
                    date_format (start_time, '%Y-%m-%d') as dt,
                    if_like,
                    if_retweet,
                    tag
                from
                    tb_user_video_log t1
                    left join tb_video_info t2 using (video_id)
                where
                    date_format (start_time, '%Y-%m-%d') between '2021-09-25' and '2021-10-03'
            ) t3
        group by
            tag,
            dt
    )
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
            t4
    ) t5
where
    dt like '%2021-10%'
order by
    tag desc,
    dt