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

# 找出（每类视频，每天）的点赞量，转发量


select * from(
            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 (
                select tag,
                       date(start_time) dt,
                       sum(if_like) like_cnt,
                       sum(if_retweet) retweet_cnt
                 from tb_user_video_log l
                 join tb_video_info i 
                 on l.video_id = i.video_id 
                 group by tag, dt
            ) t1 # 别忘了给表格重命名不然输出不了
) t2
where dt between "2021-10-01" and  "2021-10-03" 
order by tag desc, dt  #按视频类别降序、日期升序排序