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

WITH t1 AS (
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 6 preceding) as sum_like_cnt_7d,
      max(sum(if_retweet)) over (partition by tag order by date_format(start_time,"%Y-%m-%d") rows 6 preceding) as max_retweet_cnt_7d
      from tb_user_video_log as u
      left join tb_video_info as v 
      on u.video_id = v.video_id 
      where datediff("2021-10-03",date(start_time))<9
--       where datediff(date((select max(start_time) from tb_user_video_log)),date(start_time))<9
      group by dt,tag 
      order by dt 
)
select * from t1 where dt BETWEEN "2021-10-01" and "2021-10-03" order by tag desc ,dt asc;