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

select 
    author,
    month,
    fans_growth_rate,
    sum(month_fans) over (partition by author order by month) as total_fans
from(
    select 
        author,
        date_format(start_time,'%Y-%m') as month,
        round((sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0)))/count(*),3) as fans_growth_rate,
        sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0)) as month_fans
    from
        tb_user_video_log as a
    left join tb_video_info as b
    on a.video_id = b.video_id
    where year(start_time) = 2021
    group by author,date_format(start_time,'%Y-%m')) as c
order by author,total_fans