select t1.author,
date_format(t2.start_time,'%Y-%m') as month,
round((sum(case when t2.if_follow = 1 then 1 else 0 end) - sum(case when t2.if_follow = 2 then 1 else 0 end)) / count(t2.start_time),3) as 	fans_growth_rate,

#核心难点：第一个sum是针对“同1天存在多条视频观看记录”，汇总其粉丝量（以天为单位进行汇总，得到每1天涨粉量）；第二个sum是针对经过group by分组之后，“同1月份存在多条视频观看记录”，汇总其粉丝量（以每1天粉丝量为基础，按照月＆作者为单位进行汇总，得到每个作者每个月的涨粉量）
sum(sum(case when t2.if_follow = 1 then 1 when t2.if_follow =2 then -1 else 0 end)) over(partition by t1.author order by date_format(t2.start_time,'%Y-%m')) as total_fans
from tb_video_info as t1
left join tb_user_video_log as t2
on t1.video_id = t2.video_id
where extract(year from t2.start_time) = '2021'
group by 1,2
having month is not null
order by 1,4