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

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