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

# 思路：先把所需字段tag连接到tb_user_video_log表中，然后使用自连接将属于国庆头三天某天近一周的数据作相对应连接
with t as(
    select tl.*, tag
    from tb_user_video_log tl
    join (
        select video_id, tag
        from tb_video_info
    ) k1
    on tl.video_id=k1.video_id
)

select tag, tag_dt dt, sum(like_cnt) sum_like_cnt_7d, max(retweet_cnt) max_retweet_cnt_7d
from (
    select t1.tag, tag_dt, date(t2.start_time) dt, sum(t2.if_like) like_cnt,
    sum(t2.if_retweet) retweet_cnt
    from (
        select distinct tag, date(start_time) tag_dt
        from t
        where date(start_time) in ('2021-10-01','2021-10-02','2021-10-03')
    ) t1
    join t t2
    on datediff(tag_dt,date(t2.start_time))<7 and datediff(tag_dt,date(t2.start_time))>=0 and t1.tag = t2.tag
    group by t1.tag, tag_dt, date(t2.start_time)
) k
group by tag, tag_dt
order by tag desc, dt;