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

1.首先我们观察到最后的日期结果是'%Y-%m'的形式，且明显要用到聚合，因此先重新整合一下第一张表备用：

select video_id,date_format(start_time,'%Y-%m') start_time,if_follow
from tb_user_video_log
 where year(start_time) = 2021

2.本题的难点一就在于能不能利用条件count来区分if_follow等于0、1、2的情况，因此我们用到

count(if_follow = 1 or null)

表示如果if_follow = 1 则放入计数，否则就不放入计数。即满足条件的放入计数。

难点二在于阶梯计算（此题为向上累积）窗口函数。

sum(count(if_follow = 1 or null)-count(if_follow = 2 or null))
over(partition by author order by start_time)
#依据我的理解：sum()内的表示涨掉粉之差。也是要向上累积的对象。
			  over()内partition by author表示根据author分组，即如果author为901的累积结束，
              902就重新开始计数，如果不根据author分组，会一直累加下去。
              order by month表示根据month的升序累加。（默认asc，升序方向的累加，即向上累积。
              如果是order by month desc即是向下累积）

完整代码如下：

select author,
start_time month,
round((count(if_follow = 1 or null)-count(if_follow = 2 or null))/count(start_time),3) AS fans_growth_rate,
sum(count(if_follow = 1 or null)-count(if_follow = 2 or null))over(partition by author order by start_time) AS total_fans
from
    (select video_id,date_format(start_time,'%Y-%m') start_time,if_follow
    from tb_user_video_log
    where year(start_time) = 2021
	) AS a 
JOIN tb_video_info AS b
ON a.video_id = b.video_id
group by author,start_time
order by author,total_fans