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

## 统计每个粉丝每个月份的涨粉率以及截止当月的总粉丝量。

select  A.author,
        A.month,
        round((A.addFanscnt-A.subtrFanscnt)/A.videoCnt,3) fans_growth_rate,
        sum(addFansMonth) over(partition by A.author order by A.month) total_fans 
    from (
   select tvi.author,
          date_format(tvl.start_time,'%Y-%m') month ,
          count(if(tvl.if_follow=1,1,null)) addFanscnt,
          count(if(tvl.if_follow=2,1,null)) subtrFanscnt,
          count(tvl.start_time) videoCnt,
          count(if(tvl.if_follow=1,1,null))-count(if(tvl.if_follow=2,1,null)) addFansMonth 
     from tb_user_video_log tvl join tb_video_info tvi using(video_id) 
    where year(tvl.start_time) = 2021 
    group by tvi.author,date_format(tvl.start_time,'%Y-%m')  
    ) A 
    order by author,total_fans ;