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

with day_table as (
    select tag,
        date_format(start_time,'%Y-%m-%d') as dt,
        sum(if(if_like=1,1,0)) as like_cnt_1d,
        sum(if(if_retweet=1,1,0)) as retweet_cnt_1d
    from tb_user_video_log as t1
    left join tb_video_info as t2 on t1.video_id=t2.video_id
    where date_format(start_time,'%Y%m%d')<=20211003 and date_format(start_time,'%Y%m%d')>=20210925 
    group by tag,dt
)

select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
from (
   select tag,dt,
   sum(like_cnt_1d) over (partition by tag order by dt rows between 6 preceding and current row) as sum_like_cnt_7d,
   max(retweet_cnt_1d) over (partition by tag order by dt rows between 6 preceding and current row) as max_retweet_cnt_7d
from day_table
group by tag,dt
) as t
where dt between '2021-10-01' and '2021-10-03'
group by tag,dt
order by tag desc,dt;