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

大力出奇迹写法，你就说管不管用吧

with a as(
     select tag,day dt,sum(if(if_like=1,1,0)) sum_day_like,sum(if(if_retweet=1,1,0)) sum_day_retweet
    from(
        select tag,date_format(start_time,'%y-%m-%d') day,if_like,if_retweet
        from tb_user_video_log join tb_video_info using(video_id)
        where datediff(start_time,'2021-9-25')>=0 and start_time like '2021%' and datediff(start_time,'2021-10-03')<=0
    )b
    group by tag,day
)

select *
from(
    select tag,'2021-10-01' dt,sum(wl),max(sr)
                from(
                    select sum_day_like wl,sum_day_retweet sr,tag
                    from a
                    where datediff('2021-10-01',dt)<=6 and datediff('2021-10-01',dt)>=0
                )b
                group by tag
    union all
    select tag,'2021-10-02' dt,sum(wl),max(sr)
                from(
                    select sum_day_like wl,sum_day_retweet sr,tag
                    from a
                    where datediff('2021-10-02',dt)<=6 and datediff('2021-10-02',dt)>=0
                )b
                group by tag
    union all
    select tag,'2021-10-03' dt,sum(wl),max(sr)
                from(
                    select sum_day_like wl,sum_day_retweet sr,tag
                    from a
                    where datediff('2021-10-03',dt)<=6 and datediff('2021-10-03',dt)>=0
                )b
                group by tag
)c
order by tag desc,dt asc