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

with t1 as (
    select author,mid(start_time,1,7) month,
    count(start_time) count_,
    sum(if(if_follow=1,1,0)) yes,
    sum(if(if_follow=2,1,0)) no
    from tb_user_video_log tl,tb_video_info ti
    where tl.video_id = ti.video_id 
    and year(start_time) = 2021
    group by author,month
)
select author,month,round((sum(yes-no)/count_),3) fans_growth_rate,
sum(yes-no) over(partition by author order by month) as total_fans
from t1 
group by author,month
order by author,total_fans