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

with
    t1 as (
        SELECT
            a.if_retweet,
            a.if_like,
            a.start_time,
            a.end_time,
            b.author,
            b.tag
        FROM
            tb_user_video_log a
            JOIN tb_video_info b ON a.video_id = b.video_id
    ),
    t2 as (
        select
            tag,
            date_format (end_time, '%Y-%m-%d') as dt,
            sum(if_like) as like_cnt,
            sum(if_retweet) as retweet_cnt
        from
            t1
        group by
            tag,
            date_format (end_time, '%Y-%m-%d')
    ),
    t3 as (
        select
            tag,
            dt,
            sum(like_cnt) over (
                PARTITION by
                    tag
                order by
                    dt rows 6 preceding
            ) as sum_like_cnt_7d,
            max(retweet_cnt) over (
                PARTITION by
                    tag
                order by
                    dt rows 6 preceding
            ) as max_retweet_cnt_7d
        from
            t2
    )
select
    tag,
    dt,
    sum_like_cnt_7d,
    max_retweet_cnt_7d
from
    t3
where
    dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY
    tag DESC,
    dt ASC;

#160题

-- 思路，先进行并表

-- 之后把条件限定，2021年，往后倒推一周

-- 之后对点赞量按照标签和时间汇总,对每个时间点进行并表操作

-- 之后找到一周的累计值和最大值即可