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

SELECT tag,'2021-10-01' AS dt,SUM(like_cnt) AS sum_like_cnt_7d,MAX(retweet_cnt) AS max_retweet_cnt_7d
FROM(
    SELECT tag,SUM(if_like=1) AS like_cnt,SUM(if_retweet=1) AS retweet_cnt,DATE(start_time) AS TIME
FROM tb_user_video_log LEFT JOIN tb_video_info USING (video_id)
WHERE start_time BETWEEN DATE_SUB('2021-10-01',INTERVAL 6 DAY ) AND '2021-10-02'
GROUP BY tag,TIME
        )
AS T
GROUP BY tag
union all
SELECT tag,'2021-10-02' AS dt,SUM(like_cnt) AS sum_like_cnt_7d,MAX(retweet_cnt) AS max_retweet_cnt_7d
FROM(
    SELECT tag,SUM(if_like=1) AS like_cnt,SUM(if_retweet=1) AS retweet_cnt,DATE(start_time) AS TIME
FROM tb_user_video_log LEFT JOIN tb_video_info USING (video_id)
WHERE start_time BETWEEN DATE_SUB('2021-10-02',INTERVAL 6 DAY ) AND '2021-10-03'
GROUP BY tag,TIME
        )
AS T
GROUP BY tag
union all
SELECT tag,'2021-10-03' AS dt,SUM(like_cnt) AS sum_like_cnt_7d,MAX(retweet_cnt) AS max_retweet_cnt_7d
FROM(
    SELECT tag,SUM(if_like=1) AS like_cnt,SUM(if_retweet=1) AS retweet_cnt,DATE(start_time) AS TIME
FROM tb_user_video_log LEFT JOIN tb_video_info USING (video_id)
WHERE start_time BETWEEN DATE_SUB('2021-10-03',INTERVAL 6 DAY ) AND '2021-10-04'
GROUP BY tag,TIME
        )
AS T
GROUP BY tag
ORDER BY tag DESC,dt ASC;