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

这个题比较简单，只注意一下只统计2021年的数据，我提交没注意到2021错了一次。

层次从内向外

第一层：join 两张表，过滤掉不需要的数据，然后分组，分组的时候可以直接计算出fans_growth_rate，同时计算出monthTotalFans，这个是这个月增加的粉丝数量，在第二层开窗统计需要用到。

第二层，开个窗，统计截止到当前月份用户的粉丝数量。

题目中描述还是清晰，明确，但是并没有说明一点，是否存在跳跃月份的问题

#2021  每个创作者,每个月

select
    c.author,
    c.month,
    c.fans_growth_rate, 
    sum(c.monthTotalFans) over(partition by c.author order by c.month) as total_fans
from 
(
    select

    a.author,
    date_format(b.start_time,"%Y-%m") as month,
    format(sum(case when b.if_follow = 1 then 1 when b.if_follow = 2 then -1 else 0 end)/count(1),3) as fans_growth_rate,
    sum(case when b.if_follow = 1 then 1 when b.if_follow = 2 then -1 else 0 end) as monthTotalFans
    from tb_video_info a join tb_user_video_log b on a.video_id = b.video_id and year(b.start_time)=2021

    group by a.author,date_format(b.start_time,"%Y-%m")
) c
order by c.author, total_fans