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

select
tag
,dt
,sum_like_cnt_7d
,max_retweet_cnt_7d
from(
    select
    tag
    ,dt 
    ,sum(like_cnt)over(partition by tag rows between 6 preceding and current row) sum_like_cnt_7d
    ,max(retweet_cnt)over(partition by tag rows between 6 preceding and current row) max_retweet_cnt_7d
    from(
        select
        tag
        ,left(start_time, 10) as dt
        ,sum(if_like) like_cnt
        ,sum(if_retweet) retweet_cnt
        from tb_user_video_log log
        left join tb_video_info info 
        on log.video_id = info.video_id
        where left(start_time, 4) = '2021'
        group by tag, dt
    ) a 
) b
where dt in ('2021-10-01', '2021-10-02', '2021-10-03')
group by tag, dt
order by tag desc, dt