牛客大厂sql160

-- todo 1 连接表，筛选出2021年国庆头三天
with t1 as(
    select tvi.tag,
           date(tuvl.start_time) dt,
           if_like,
           if_retweet
    from tb_user_video_log tuvl
    left join tb_video_info tvi on tuvl.video_id = tvi.video_id
    where date(tuvl.start_time) >= date_add('2021-10-01',interval -6 day) and date(tuvl.start_time) <= '2021-10-03'
)
-- todo 2 2021年9月25到10月3日每天的点赞量和转发量
,t2 as(
    select tag,
           dt,
           count(if(if_like = 1,1,null)) like_cnt,
           count(if(if_retweet = 1,1,null)) retweet_cnt
    from t1
    group by tag, dt
)
-- todo 3 2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量
,t3 as(
   select tag,
           dt,
           sum(like_cnt) over(partition by tag order by dt rows between 6 preceding and current row) as sum_like_cnt_7d,
           max(retweet_cnt) over(partition by tag order by dt rows between 6 preceding and current row) as max_retweet_cnt_7d
    from t2
)
select *
from t3
where dt >= '2021-10-01' and dt <= '2021-10-03'
order by tag desc,dt asc
;