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

先求每月的总变化数量，再叠加上个月的就是总粉丝量。

with t1 as(
    select
        author,
        date_format(start_time,'%Y-%m') as `month`,
        round(sum(if(if_follow=2,-1,if_follow))/count(start_time),3) as fans_growth_rate,
        sum(if(if_follow=2,-1,if_follow)) as month_total_fans
    from tb_user_video_log as t1
    join tb_video_info as t2 on t1.video_id=t2.video_id
    where year(start_time) = 2021
    group by author,date_format(start_time,'%Y-%m')
)
select
    author,
    month,
    fans_growth_rate,
    sum(month_total_fans) over(partition by author order by month) as total_fans
from t1
order by author,total_fans;