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

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
    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