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

with 
t1 as(
    select
        i.tag,
        date_format(p.start_time,'%Y-%m-%d') as dt,
        sum(p.if_like) as like_cnt,
        sum(p.if_retweet) as retweet_cnt
    from tb_user_video_log p left join tb_video_info i using(video_id)
    where date_format(p.start_time,'%Y-%m-%d') between '2021-09-25' and '2021-10-03'
    group by i.tag,date_format(p.start_time,'%Y-%m-%d')),
t2 as(
    select
        tag,
        sum(1001_like_cnt) as 1001_sum_like_cnt_7d,
        sum(1002_like_cnt) as 1002_sum_like_cnt_7d,
        sum(1003_like_cnt) as 1003_sum_like_cnt_7d,
        max(1001_retweet_cnt) as 1001_max_retweet_cnt_7d,
        max(1002_retweet_cnt) as 1002_max_retweet_cnt_7d,
        max(1003_retweet_cnt) as 1003_max_retweet_cnt_7d
    from(
        select
            tag,
            dt,
            case when dt between '2021-09-25' and '2021-10-01' then like_cnt else 0 end as 1001_like_cnt,
            case when dt between '2021-09-26' and '2021-10-02' then like_cnt else 0 end as 1002_like_cnt,
            case when dt between '2021-09-27' and '2021-10-03' then like_cnt else 0 end as 1003_like_cnt,
            case when dt between '2021-09-25' and '2021-10-01' then retweet_cnt else 0 end as 1001_retweet_cnt,
            case when dt between '2021-09-26' and '2021-10-02' then retweet_cnt else 0 end as 1002_retweet_cnt,
            case when dt between '2021-09-27' and '2021-10-03' then retweet_cnt else 0 end as 1003_retweet_cnt
        from t1) a
        group by tag
        order by tag),
t3 as(select tag,dt from t1 where dt between '2021-10-01' and '2021-10-03' order by tag,dt),
t4 as(
    select
        t3.tag,
        t3.dt,
        if(t3.dt='2021-10-01',t2.1001_sum_like_cnt_7d,0) as 1001_sum_like_cnt_7d,
        if(t3.dt='2021-10-02',t2.1002_sum_like_cnt_7d,0) as 1002_sum_like_cnt_7d,
        if(t3.dt='2021-10-03',t2.1003_sum_like_cnt_7d,0) as 1003_sum_like_cnt_7d,
        if(t3.dt='2021-10-01',t2.1001_max_retweet_cnt_7d,0) as 1001_max_retweet_cnt_7d,
        if(t3.dt='2021-10-02',t2.1002_max_retweet_cnt_7d,0) as 1002_max_retweet_cnt_7d,
        if(t3.dt='2021-10-03',t2.1003_max_retweet_cnt_7d,0) as 1003_max_retweet_cnt_7d
    from t3 left join t2 using(tag))

select
    tag,
    dt,
    1001_sum_like_cnt_7d+1002_sum_like_cnt_7d+1003_sum_like_cnt_7d as sum_like_cnt_7d,
    greatest(1001_max_retweet_cnt_7d,1002_max_retweet_cnt_7d,1003_max_retweet_cnt_7d) as max_retweet_cnt_7d
from 
    t4
order by
    tag desc,dt