题解 | 每个创作者每月的涨粉率及截止当前的总粉丝量

SELECT
	author,
	MONTH,
	fans_growth_rate,
	sum( net_fans ) over ( PARTITION BY author ORDER BY MONTH ) AS total_fans 
FROM
	(
	SELECT
		author,
		DATE_FORMAT( start_time, '%Y-%m' ) AS MONTH,
		ROUND(
			(
				SUM(
				IF
					( if_follow = 1, 1, 0 )) - SUM(
				IF
				( if_follow = 2, 1, 0 ))) / COUNT(*),
			3 
		) AS fans_growth_rate,
		SUM(
		IF
			( if_follow = 1, 1, 0 )) - SUM(
		IF
		( if_follow = 2, 1, 0 )) AS net_fans 
	FROM
		tb_user_video_log u
		LEFT JOIN tb_video_info v ON u.video_id = v.video_id 
	WHERE
		YEAR ( start_time ) = 2021
		
	GROUP BY
		author,
	DATE_FORMAT( start_time, '%Y-%m' )) t 
ORDER BY
	1,4