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

select 
  author,
  t3.month,
  round(sum(if(if_follow=2,-1,if_follow))/count(*),3) as fans_growth_rate,
  max(cnt) as total_fans
from 
  (
    select 
      author,
      date_format(start_time,'%Y-%m') as "month",
      if_follow,
      sum(if(if_follow=2,-1,if_follow)) over(partition by author order by date_format(start_time,'%Y-%m')) as cnt
    from 
      tb_user_video_log as t1
    join 
      tb_video_info as t2
    on 
      t1.video_id=t2.video_id
    where 
      year(start_time)=2021
  ) as t3
group by 
  author,
  t3.month
order by 
  author,
  total_fans