#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