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

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