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

select
    author,
    month,
    round(fans_growth_rate,3),
    sum(rollnum) over (
        partition by
            author
        order by
            month
    ) total_fans
from
    (
        select
            author,
            date_format (start_time, '%Y-%m') month,
            sum(if (if_follow = 2, -1, if_follow)) / count(1)     fans_growth_rate,
            sum(if (if_follow = 2, -1, if_follow)) as rollnum
        from
            tb_user_video_log
            join tb_video_info using (video_id)
            where year(start_time) = 2021
        group by
            author,
            month
    ) t1
order by  author ,total_fans

本题难度在sql进阶知识点中已经有提及，在sum()函数中增加over()窗口函数 partition by author order by month就可以计算累计月份的粉丝数量了。