题解 | #每类视频近一个月的转发量/率#

SELECT tag,
       SUM(if_retweet)                               AS retweet_cut,
       ROUND(SUM(if_retweet) / COUNT(start_time), 3) AS retweet_rate
FROM tb_video_info a LEFT JOIN
( select
video_id,
start_time,
if_retweet,
max(date(start_time)) OVER () as max_day
from tb_user_video_log b
WHERE if_follow = 1
   OR if_like = 1
   OR if_retweet = 1
   OR if_retweet = 1
   OR comment_id IS NOT NULL ) c
ON a.video_id = c.video_id
where datediff(max_day,date(start_time)) <=29
GROUP BY tag
order by retweet_rate desc;