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

SELECT 
    author, 
    date_format(start_time,'%Y-%m') month,
    round(sum(case when if_follow=1 then 1
             when if_follow=2 then -1
             else 0 end)/count(author),3) 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')) total_fans
FROM tb_user_video_log log 
left join tb_video_info info on log.video_id=info.video_id
where year(start_time)=2021
group by author,month
order by author,total_fans  

本题最大的难点在于累加粉丝数，这个地方要用窗口函数才能实现累加功能。

使用窗口函数的时候要注意：

1. author是大类，月份是累加单位，所以partition by author, order by date
2. 两层sum是因为第一个sum是针对每一个月内进行计算，第二个sum是为了每一个author在不同月份的累加。