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

先存储25-3号每天的点赞转发情况（res）：

with res as (
    select 
        tag,
        DATE(start_time) as 'dt',
        sum(if_like) as 'likeNum',
        sum(if_retweet) as 'RNum'
    from tb_user_video_log as A
    left join tb_video_info as B
    using(video_id)
    where DATE(start_time) BETWEEN '2021-09-25' AND '2021-10-03'
    group by tag,dt
)

再限定到1-3号，用每天去选择时间差为7天的数据

datediff(A.dt,B.dt)>=0 and datediff(A.dt,B.dt)<=6)

之后根据要求，得出点赞总数和最高转发量即可，以下为总代码

with res as (
    select 
        tag,
        DATE(start_time) as 'dt',
        sum(if_like) as 'likeNum',
        sum(if_retweet) as 'RNum'
    from tb_user_video_log as A
    left join tb_video_info as B
    using(video_id)
    where DATE(start_time) BETWEEN '2021-09-25' AND '2021-10-03'
    group by tag,dt
)
select 
    tag,
    dt,
    (select sum(likeNum) 
     from res as B
     where A.tag = B.tag 
     and datediff(A.dt,B.dt)>=0 and datediff(A.dt,B.dt)<=6) as 'like_cnt',
     (select max(RNum) 
     from res as C
     where A.tag = C.tag 
     and datediff(A.dt,C.dt)>=0 and datediff(A.dt,C.dt)<=6) as 'retweet_cnt'
from res as A
where dt  BETWEEN '2021-10-01' AND '2021-10-03'