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

我先把各个作者各个月份的播放量（num）,本月粉丝增加量(fans)存储为res:

with res as (
    select
        author,
        date_format(end_time,'%Y-%m') as 'month',
        count(1) as 'num',
        sum(if(if_follow=2,-1,if_follow)) as 'fans'
    from tb_user_video_log as A
    left join tb_video_info as B
    using(video_id)
    group by author,month
)

根据res再计算当前粉丝和涨粉率，以及对年份进行筛选和排序

select 
    author,
    month,
    round(fans/num,3) as 'fans_growth_rate',
    (select sum(fans) from res as t where t.month <= A.month and t.author = A.author) as 'total_fans'
from res as A
where month like '2021%'
order by author,total_fans