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

select author,
month,
round((addfans)/cnt,3) as fans_growth_rate,
sum(addfans) over(partition by author order by author,month) as total_fans
from(
    select
    date_format(start_time,'%Y%-%m') as month,
    sum(if(if_follow=2,-1,if_follow)) as addfans,author,
    count(*) as cnt
    from tb_user_video_log
    join tb_video_info using(video_id)
    group by author,month
)a
where month >='2020-12'
order by author,total_fans