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

select
    author,
    month,
    round(fans_inc/bofang,3) fans_growth_rate,
    sum(fans_inc) over(partition by author order by month ) total_fans 
from 
    (select    
        b.author,
        date_format(a.start_time,'%Y-%m') month,
        sum(case when a.if_follow=2 then -1 else a.if_follow end) fans_inc,
        count(1) as bofang
    from 
        tb_user_video_log a 
    left join 
        tb_video_info b 
    on 
        a.video_id=b.video_id
    where 
         year(a.start_time)=2021
    group by 
        b.author,
        month) aa
order by 
    author,
    total_fans