题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
如果考虑有的天没有播放量,可以用range between:
select t.* from( select tag,dt, sum(sum_like_cnt_7d) over w as sum_like_cnt_7d, max(max_retweet_cnt_7d) over w as max_retweet_cnt_7d from (select tag,date(start_time) as dt, sum(if_like) as sum_like_cnt_7d, sum(if_retweet) as max_retweet_cnt_7d from tb_user_video_log b left join tb_video_info c using(video_id) where date(start_time) between '2021-09-25' and '2021-10-03' group by 1,2)t1 window w as (partition by tag order by dt asc range between interval 6 day preceding and current row) )t group by 1,2 having dt between '2021-10-01' and '2021-10-03' order by tag desc,dt asc