with t as (
  SELECT author, 
         month, 
         round(avg(if_follow), 3) as fans_growth_rate, 
         sum(if_follow) as `month_follow`
  FROM (
		   SELECT video_id, 
		          if(if_follow = 2, -1, if_follow) as `if_follow`, 
		          DATE_FORMAT(start_time,'%Y-%m') as month
           FROM tb_user_video_log
           WHERE YEAR(start_time)=2021) as T
   left join tb_video_info as I
   on T.video_id = I.video_id
   GROUP BY author, month
 )
SELECT author,month,fans_growth_rate, (
    SELECT SUM(month_follow)
    FROM t as t1
    where t0.author=t1.author and t1.month<=t0.month
    ) as total_fans
FROM t as t0
ORDER BY author,total_fans;

1. 第一步

查询2021年的数据，并将if_follow中的取消关注值改为 -1

SELECT 	video_id, 
		if(if_follow = 2, -1, if_follow) as `if_follow`, 
		DATE_FORMAT(start_time,'%Y-%m') as month
FROM tb_user_video_log
WHERE YEAR(start_time)=2021

2. 第二步

按作者和月份进行分组，并统计当月涨粉率以及当月粉丝量

3. 第三步

进行关联查询，计算截止目前位置的粉丝总量。