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

with t1 as (
    select 
        video_id,author,tag,end_time,if_follow,if_like,if_retweet,comment_id
    from
        tb_video_info s1
    left join 
        tb_user_video_log s2
    using(video_id)

)
,t2 as(
    select tag,
    date(end_time) dt
from
    t1
where date(end_time)between '2021-10-01' and '2021-10-03'
group by 1,2
order by 1,2
)
,t3 as(
    select t2.tag,t2.dt,date(t1.end_time) daily,
            sum(if_like) daily_like,
            sum(if_retweet) daily_retweet
    from t2
    left join 
        t1
    on t1.tag = t2.tag
    and datediff(t2.dt,date(t1.end_time)) between 0 and 6
    group by 1,2,3
)
select
    tag,
    dt,
    sum(daily_like) sum_like_cnt_7d,
    max(daily_retweet) max_retweet_cnt_7d
from
    t3
group by 1,2
order by 1 desc,2 asc

傻呗题目，浪费老子时间