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

关键：两次聚合函数的使用！

一次用于group by，一次用于窗口函数求累积和

select author,
			 month,
			 round(sum(if_follow)/count(start_time),3) as fans_growth_rate,
			 sum(sum(if_follow)) over(partition by author order by month asc) as total_fans
from (
			select author,start_time,
						 date_format(start_time,'%Y-%m') as month,
						 case when if_follow=2 then -1
									#when if_follow=1 then 1
									else if_follow
									end as if_follow
			from tb_user_video_log
			left join tb_video_info using(video_id)
			where year(start_time)=2021
			) as t
group by author,month
order by author,total_fans