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

WITH t1 AS (
    SELECT
    *,
    DATE (start_time) AS dt
    FROM
    tb_user_video_log
    WHERE DATE (start_time) BETWEEN '2021-09-25' AND '2021-10-03'
),
t2 AS (
    SELECT
    tv.tag,
    t1.dt,
    t1.if_like,
    t1.if_retweet
    FROM t1
    LEFT JOIN tb_video_info tv
    on t1.video_id = tv.video_id
),
t3 AS (
    SELECT
    tag,
    dt,
    SUM(if_like) AS sum_like_cnt_1d,
    SUM(if_retweet) AS sum_retweet_cnt_1d
    FROM t2
    GROUP BY tag,dt
),
t4 AS (
    SELECT
    tag,
    '2021-10-01' AS dt,
    SUM(sum_like_cnt_1d) AS sum_like_cnt_7d,
    MAX(sum_retweet_cnt_1d) AS max_retweet_cnt_7d
    FROM t3
    WHERE dt BETWEEN '2021-09-25' AND '2021-10-01'
    GROUP BY tag
    UNION ALL
    SELECT
    tag,
    '2021-10-02' AS dt,
    SUM(sum_like_cnt_1d) AS sum_like_cnt_7d,
    MAX(sum_retweet_cnt_1d) AS max_retweet_cnt_7d
    FROM t3
    WHERE dt BETWEEN '2021-09-26' AND '2021-10-02'
    GROUP BY tag
    UNION ALL
    SELECT
    tag,
    '2021-10-03' AS dt,
    SUM(sum_like_cnt_1d) AS sum_like_cnt_7d,
    MAX(sum_retweet_cnt_1d) AS max_retweet_cnt_7d
    FROM t3
    WHERE dt BETWEEN '2021-09-27' AND '2021-10-03'
    GROUP BY tag
)
SELECT * FROM t4
ORDER BY tag DESC, dt ASC;

# 主要思路还是，datetime类型一般不要substr进行日期截取，可以使用date函数！
# 记得结果进行排序！