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

select distinct
    *
from(
        select
            day_cnt.tag
            , dt
            , sum(like_cnt) over (partition by tag order by dt range interval 6 day preceding) as sum_like_cnt_7d
            , max(retweet_cnt) over (partition by tag order by dt range interval 6 day preceding) as max_retweet_cnt_7d   
        from(
            select
                vi.tag
                , date(uv.start_time) as dt
                , sum(uv.if_like) as like_cnt
                , sum(uv.if_retweet) as retweet_cnt 
            from tb_video_info as vi
            left join tb_user_video_log as uv on uv.video_id = vi.video_id
            group by vi.tag, date(uv.start_time)
            ) as day_cnt
    ) as t1
where t1.dt between "2021-10-01" and "2021-10-03"
order by t1.tag desc, t1.dt