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

SELECT t2.tag,SUM(t1.if_retweet) retweet_cnt,ROUND(SUM(t1.if_retweet)/COUNT(CASE WHEN t1.end_time-t1.start_time>0 THEN 1 ELSE NULL END),3) retweet_rate
FROM tb_user_video_log t1
INNER JOIN tb_video_info t2
ON t1.video_id=t2.video_id
WHERE DATE(t1.start_time) BETWEEN DATE_ADD( (select max(t1.start_time) mtime from tb_user_video_log t1),INTERVAL -30 DAY) AND (select max(t1.start_time) mtime from tb_user_video_log t1)
GROUP BY t2.tag
ORDER BY retweet_rate DESC;