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

SELECT 
tag,sum(if_retweet) as retweet_cut,ROUND(sum(if_retweet)/count(video_id),3) as retweet_rate
FROM (
		SELECT  
		u.video_id,u.if_retweet,v.tag
		FROM
		tb_user_video_log u 
		left  JOIN  tb_video_info v
		on u.video_id = v.video_id
		 JOIN (
		SELECT 
		 adddate(MAX(date(start_time)),-29) as month
		FROM
		tb_user_video_log u 
		left JOIN  tb_video_info v
		on u.video_id = v.video_id
		) a 
		on  u.start_time > a.month
) a
GROUP BY tag
ORDER BY retweet_rate desc