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

select*
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) dt,
        sum(if_like) like_cnt,
        sum(if_retweet) retweet_cnt
        from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id
        group by 1,2
    ) as c
    group by 1,2
)d
where dt in ('2021-10-01','2021-10-02','2021-10-03')
order by 1 desc,2