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

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 between 6 preceding and current row) as sum_like_cnt_7d
    ,max(sum(if_retweet)) over(partition by tag order by date_format(start_time,'%Y-%m-%d') rows between 6 preceding and current row) as max_retweet_cnt_7d
    from tb_user_video_log log
    left join tb_video_info info
    using(video_id)
    where date_format(log.start_time,'%Y-%m-%d')<'2021-10-04'
    group by info.tag,date_format(start_time,'%Y-%m-%d')
    order by tag desc,dt
) t
where dt between '2021-10-01' and '2021-10-03'