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

select
    author,
    month,
    round(avg(f), 3) fans_growth_rate,
    sum(sum(f)) over (
        partition by
            author
        order by
            month
    ) total_fans
from
    (
        select
            author,
            date_format (start_time, '%Y-%m') month,
            case
                when if_follow > 1 then -1
                else if_follow
            end f
        from
            tb_user_video_log l
            inner join tb_video_info i on l.video_id = i.video_id
        where
            year (start_time) = 2021
    ) t
group by
    author,
    month
order by
    author,
    total_fans