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

select
    author,
    date_format (start_time, '%Y-%m') as month,
    round(
        sum(if (if_follow = 2, -1, if_follow)) / count(author),3
    ) fans_grouth_rate,
    sum(sum(if (if_follow = 2, -1, if_follow))) 
        over (partition by author order by date_format (start_time, '%Y-%m')) total_fans
-- 两层sum是因为第一个sum是针对每个月进行计算，第二个sum是为了每一个author在不同月份的累加
from tb_user_video_log t1 
    left join tb_video_info t2 on t1.video_id = t2.video_id
where year(start_time)=2021
group by
    author,
    month
order by
    author,
    total_fans