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

with tmp as(
        select   tag
                ,date(start_time) as dt
                ,sum(if_like) as daily_like #每一类视频每天的点赞量
                ,sum(if_retweet) as daily_retweet #每一类视频每天的转发量
        from  tb_user_video_log
        left join tb_video_info
        using(video_id)
        where  date(start_time) between '2021-09-25' and '2021-10-03'
        group by tag,date(start_time)    
)


select  tag
       ,dt
       ,sum_like_cnt_7d
       ,max_retweet_cnt_7d
from
(select  tag
       ,dt
       ,sum(daily_like) over(partition by tag order by dt rows between 6 preceding and current row ) as sum_like_cnt_7d
       ,max(daily_retweet) over(partition by tag order by dt rows between 6 preceding and current row ) as max_retweet_cnt_7d
from tmp
) as t2
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt