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

select *
from
(select b.tag,max(b.start_time),sum(b.su_like),max(b.su_ret)
    from
      (select a.tag, a.start_time,sum(a.if_like) su_like,sum(a.if_retweet) su_ret
        from
        (select v.tag, DATE_FORMAT (u.start_time, '%Y-%m-%d') start_time,u.if_like, u.if_retweet
            from tb_user_video_log u join tb_video_info v on u.video_id = v.video_id
        ) a
        where a.start_time > date_sub('2021-10-01', interval 7 day) and a.start_time <= '2021-10-01'
        group by a.tag, a.start_time
       ) b
    group by b.tag #这一部分是每类视频2021-10-01的近一周总点赞量和一周内最大单天转发量
union
select b.tag,max(b.start_time),sum(b.su_like),max(b.su_ret)
    from
      (select a.tag, a.start_time,sum(a.if_like) su_like,sum(a.if_retweet) su_ret
        from
        (select v.tag, DATE_FORMAT (u.start_time, '%Y-%m-%d') start_time,u.if_like, u.if_retweet
            from tb_user_video_log u join tb_video_info v on u.video_id = v.video_id
        ) a
        where a.start_time > date_sub('2021-10-02', interval 7 day) and a.start_time <= '2021-10-02'
        group by a.tag, a.start_time
       ) b
    group by b.tag #这一部分是每类视频2021-10-02的近一周总点赞量和一周内最大单天转发量
union
select b.tag,max(b.start_time),sum(b.su_like),max(b.su_ret)
    from
      (select a.tag, a.start_time,sum(a.if_like) su_like,sum(a.if_retweet) su_ret
        from
        (select v.tag, DATE_FORMAT (u.start_time, '%Y-%m-%d') start_time,u.if_like, u.if_retweet
            from tb_user_video_log u join tb_video_info v on u.video_id = v.video_id
        ) a
        where a.start_time > date_sub('2021-10-03', interval 7 day) and a.start_time <= '2021-10-03'
        group by a.tag, a.start_time
       ) b
    group by b.tag #这一部分是每类视频2021-10-03的近一周总点赞量和一周内最大单天转发量
) c
order by c.tag desc

暴力解题