SELECT
B.tag AS tag,
B.dt AS dt,
B.sum_like_cnt_7d AS sum_like_cnt_7d,
B.max_between_cnt_7d AS max_between_cnt_7d
FROM
(SELECT
A.tag AS tag,
A.dt AS dt,
SUM(A.like_cnt) OVER(PARTITION BY A.tag ORDER BY A.dt ROWS 6 PRECEDING) AS sum_like_cnt_7d,
MAX(A.retweet_cnt) OVER(PARTITION BY A.tag ORDER BY A.dt ROWS 6 PRECEDING) AS max_between_cnt_7d
FROM
(SELECT
b.tag AS tag,
DATE_FORMAT(a.start_time, '%Y-%m-%d') AS dt,
SUM(a.if_like) AS like_cnt,
SUM(a.if_retweet) AS retweet_cnt
FROM
tb_user_video_log AS a
INNER JOIN
tb_video_info AS b
ON
a.video_id = b.video_id
WHERE
DATE_FORMAT(a.start_time, '%Y-%m-%d') BETWEEN '2021-09-25' AND '2021-10-03'
AND DATE_FORMAT(a.end_time, '%Y-%m-%d') BETWEEN '2021-09-25' AND '2021-10-03'
GROUP BY
tag, DATE_FORMAT(a.start_time, '%Y-%m-%d')
) AS A
) AS B
WHERE
B.dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY
B.tag DESC, B.dt ASC;

Note:

This topic examines the sliding window. In this kind of problem of how many days to take, the window function can be designed according to the needs of the topic. Take this topic as an example

Taking the situation within a week, the idea can be sorted in ascending and descending order.

1) Ascending order: 6 lines ahead of the current line

partition by tag order by dt rows 6 preceding 
partition by tag order by dt rows between 6 preceding and CURRENT row

2) descending order: 6 lines from the current line

partition by tag order by dt desc rows between  CURRENT row and 6 following

Remember that this method is very helpful for solving similar problems.

The top 3 video with the highest popularity among the videos released by SQL6 in recent months

Level: difficulty

describe

 

Existing users - video interaction table tb_user_video_log

(uid - user ID, video_id - VIDEO ID, start_time - start viewing time, end_time - end viewing time, if_follow - follow, if_like - like, if_retweet - forward, comment_id - Comment ID)

Short video message table tb_video_info

(video_id - VIDEO ID, author - creator ID, tag - category tag, duration - VIDEO duration, release_time - release time)

Question: find out the top 3 videos with the highest popularity among the videos released in recent months.

Note:

• Popularity = (a * video completion rate + b * likes + c * comments + d * forwards) * freshness;
• Freshness = 1 / (no playing days recently + 1);
• The currently configured parameters a, B, C and D are 100, 5, 3 and 2 respectively.
• Last play date in end_time - the end viewing time shall prevail. Assuming T, the statistics of the last month shall be based on the closed interval of [T-29, T].
• The heat in the results is kept as an integer and sorted in descending order of heat.

Output example:

The output of the sample data is as follows

Explanation:

The latest broadcast date is 2021-10-03, which is recorded as the date of the day; The videos released in the past month (2021-09-04 and later) include 2001, 2002, 2003 and 2004, but there is no broadcast record in 2004;

The completion rate of video 2001 is 1.0 (4 times played, 4 times completed), 3 times liked, 1 time commented, and 2 times forwarded. The number of days not played recently is 0, so the popularity is: (100 * 1.0 + 5 * 3 + 3 * 1 + 2 * 2) / (0 + 1) = 122

Similarly, the completion rate of video 2003 is 0, the number of likes is 1, the number of comments and forwarding is 0, and the number of days without playing recently is 3. Therefore, the popularity is: (100 * 0 + 5 * 1 + 3 * 0 + 2 * 0) / (3 + 1) = 1 (1.2 is reserved as an integer).

Procedure:

SELECT
video_id,
ROUND((100 * total_play_rate + 5 * like_num + 3 * comment_num + 2 * retweet_num) * (1 / (not_play_days + 1)),0) AS hot_index
FROM
(SELECT
a.video_id AS video_id,
# Video completion rate
AVG(CASE
WHEN TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) < b.duration 
THEN 0
WHEN TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) >= b.duration
THEN 1
END) AS total_play_rate,
# Number of likes
SUM(a.if_like) AS like_num,
# Number of comments
COUNT(a.comment_id) AS comment_num,
# Forwarding number
SUM(a.if_retweet) AS retweet_num,
# Recent days without broadcasting
DATEDIFF(
(
SELECT MAX(end_time) FROM tb_user_video_log),
MAX(a.end_time)
) AS not_play_days
FROM
tb_user_video_log AS a
INNER JOIN
tb_video_info AS b
ON
a.video_id = b.video_id
WHERE
DATEDIFF(
(
SELECT MAX(end_time) FROM tb_user_video_log),
b.release_time
) <= 29
GROUP BY
a.video_id) AS A
ORDER BY
hot_index DESC
LIMIT 3;

Unfinished to be continued...