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

和上一题一样，group by求每日，窗口函数求累计

select *
from (
select tag,date_format(start_time,'%Y-%m-%d') dt,
    sum(sum(if_like)) over w as  sum_like_cnt_7d,
    max(sum(if_retweet)) over w as max_retweet_cnt_7d
from tb_user_video_log t1 join tb_video_info t2 using(video_id)
group by tag,date_format(start_time,'%Y-%m-%d')
window w as (partition by tag order by date_format(start_time,'%Y-%m-%d') rows 6 preceding)
) t
where dt between date_format('2021-10-1','%Y-%m-%d') and date_format('2021-10-3','%Y-%m-%d')
order by tag desc,dt asc