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

with
    t1 as (
        SELECT
            a.if_follow,
            a.start_time,
            a.end_time,
            b.author,
            b.tag
        FROM
            tb_user_video_log a
            JOIN tb_video_info b ON a.video_id = b.video_id
        where year(end_time) =2021
    )
select
    author,
    date_format (end_time, '%Y-%m') as month,
    round(
        sum(
            case
                when if_follow = 0 then 0
                when if_follow = 2 then -1
                else 1
            end
        ) / count(end_time),
        3
    ) as fans_growth_rate,
    sum(
        sum(
            case
                when if_follow = 0 then 0
                when if_follow = 2 then -1
                else 1
            end
        )
    ) over (
        partition by
            author
        order by
            date_format (end_time, '%Y-%m')
    ) as total_fans
from
    t1
group by
    author,
    date_format (end_time, '%Y-%m')
order by author,total_fans

思路

-- 基本条件：2021年，创作者及月份作为分类标准，

-- 还需要字段，加粉量和减粉量，总粉丝量 sum函数