题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
with aa as (select date_format(a.start_time,'%Y-%m-%d') dt, sum(a.if_like) like1, sum(a.if_retweet) retweet, b.tag from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id group by b.tag,dt) select * from (select tag, dt, sum(like1) over(partition by tag order by dt rows 6 preceding) sum_like_cnt_7d, max(retweet) over(partition by tag order by dt rows 6 preceding) max_retweet_cnt_7d from aa ) bb where bb.dt between '2021-10-01' and '2021-10-03' order by bb.tag desc, bb.dt