题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
with t1 as(select tag, DATE_FORMAT(start_time,'%Y-%m-%d') as dt, sum(if_like) as like_cnt, sum(if_retweet) as retweet_cnt from tb_video_info join tb_user_video_log using(video_id) group by tag,dt order by tag,dt), t2 as(select tag,dt, sum(like_cnt) over (PARTITION by tag order by dt rows 6 preceding) sum_like_cnt_7d, max(retweet_cnt) over (partition by tag order by dt rows 6 preceding) max_retweet_cnt_7d from t1) select * from t2 where dt between '2021-10-01' and '2021-10-03' order by tag desc,dt