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

with a as(
    select
        author,
        substring(start_time,1,7) as month,
        case
        when if_follow=2 then -1
        when if_follow=1 then 1
        else 0
        end follow
    from 
        tb_user_video_log l
        join tb_video_info i on l.video_id=i.video_id
    where year(start_time)=2021
)
select
    author,
    month,
    round(sum(follow)/count(*),3) as fans_growth_rate,
    sum(sum(follow)) over(partition by author order by month) as total_fans
from a
group by author,month
order by author,total_fans
;