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

select 
    author, date_format(u.start_time,'%Y-%m') as month,
    round(
        sum(case when if_follow = 1 then 1
                 when if_follow = 2 then -1
            else 0 end)/count(author),3) as fans_growth_rate,
    sum(sum(case when if_follow = 1 then 1
                 when if_follow = 2 then -1
            else 0 end))over(partition by author order by date_format(start_time, '%Y-%m')) as total_fans
from 
    tb_user_video_log u 
left join 
    tb_video_info v
on 
    u.video_id = v.video_id
where 
    year(start_time) = 2021
group by 
    author, month
order by 
    author, total_fans