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

select author,month,fans_growth_rate,
sum(total_fans1) over (partition by author order by month) as total_fans
from
    (select author,
    substr(start_time,1,7) as month,
    round((sum(if(if_follow=1,1,0))+sum(if(if_follow=2,-1,0)))/count(t1.id),3) as 	  	fans_growth_rate,
    sum(if(if_follow=1,1,0))+sum(if(if_follow=2,-1,0)) as total_fans1
    from tb_user_video_log t1
    left join tb_video_info t2
    on t1.video_id=t2.video_id
    group by author,substr(start_time,1,7)) t3
where t3.total_fans1 !=0
order by author,total_fans;

这个题的坑就是要把涨粉为0的数据剔除