思路：
①用两个窗口函数count，sum分别算出每个作者各月的播放次数b、粉丝增量c，形成tb1；
②基于tb1再用一次窗口函数sum对每个作者每月粉丝增量做一个逐月累加即可。

select author, a, round(c/b,3), sum(c)over(partition by author order by a) d
from(
select distinct author, date_format(end_time,'%Y-%m') a,
count(video_id)over(partition by author,date_format(end_time,'%Y-%m')) b,
sum(if(if_follow=2,-1,if_follow))over(partition by author,date_format(end_time,'%Y-%m')) c
from tb_user_video_log left join tb_video_info using(video_id)
where year(end_time)=2021) as tb1
order by author, d