题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量 用了临时表，可读性好

with t as (
  select author,date_format(start_time,'%Y-%m') month
  ,sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end ) total_fans --每月粉丝量
  ,round(sum(case when if_follow=1 then 1 when  if_follow=2 then -1 else 0 end )/count(start_time),3) fans_growth_rate --每月涨粉率
  from tb_user_video_log tvl
  inner join tb_video_info tvi
  on tvl.video_id=tvi.video_id
  where year(start_time)='2021'
  group by author,date_format(start_time,'%Y-%m')
)
select author,month
,fans_growth_rate
,sum(total_fans)over(partition by author order by month ) total_fans_cum -- 截至当月累计粉丝量
from t
order by author,total_fans_cum