#2021
#per creater per month 
#growth nb(jiafeng-diaofeng) growth rate(growth nb / bofang) (round 3)

 with fans_grow_nb as (select tbv.author as author, date_format(tbu.start_time,'%Y-%m') as m,sum(case when tbu.if_follow=1 then 1 when tbu.if_follow=2 then -1 else 0 end) as fans_growth_nb ,count(*) as playtimes
from tb_video_info tbv left join tb_user_video_log tbu on tbv.video_id=tbu.video_id
where   date(tbu.start_time) between '2021-01-01' and '2021-12-31' 
and     date(tbu.end_time) between '2021-01-01' and '2021-12-31' 
group by tbv.author, date_format(tbu.start_time,'%Y-%m'))


select author,m, round(fans_growth_nb/playtimes,3) as fans_growth_rate, coalesce((sum(fans_growth_nb) over (partition by author order by m rows between unbounded preceding and current row )),fans_growth_nb) as total_fans
from fans_grow_nb
order by author,total_fans asc