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

WITH t1 AS (
	SELECT
		author,
		date_format( end_time, '%Y-%m' ) AS month,
	CASE
			WHEN if_follow = 0 THEN
			0 
			WHEN if_follow = 1 THEN
			1 ELSE - 1 
		END AS fan_grow,
		sum(	CASE
			WHEN if_follow = 1 THEN
			1 
			WHEN if_follow = 0 THEN
			0 ELSE - 1 
		END) over (PARTITION by author order by date_format( start_time, '%Y-%m' )) as total_fans
	FROM
		tb_user_video_log t1
		JOIN tb_video_info t2 ON t1.video_id = t2.video_id 
	WHERE
		YEAR ( start_time )= 2021 
	) 
	SELECT
	author,
	month,
	round((sum(fan_grow )/ count(*)),3) AS fans_grwoth_rate,
	max(total_fans ) as total_fans
FROM
	t1 
GROUP BY
	author,month
order by author,total_fans