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

 -- 第一步：获取每条观看记录 + 视频信息 + 月份
WITH
    tongji AS (
        SELECT
            DATE_FORMAT(start_time, '%Y-%m') AS month_video,
            a.if_follow, -- 用户是否关注/取关作者
            a.start_time,
            b.author,
            b.release_time
        FROM
            tb_user_video_log AS a
            LEFT JOIN tb_video_info AS b ON a.video_id = b.video_id
        WHERE
            YEAR(a.start_time) = 2021 -- 限定视频发布于 2021 年
    ),
    -- 第二步：统计每位作者在每月的粉丝净增长 + 播放次数
    monthly_growth AS (
        SELECT
            author,
            month_video AS month,
            SUM(
                CASE
                    WHEN if_follow = 1 THEN 1 -- 关注计 +1
                    WHEN if_follow = 2 THEN -1 -- 取关计 -1
                    ELSE 0
                END
            ) AS net_fans_growth,
            COUNT(*) AS view_count
        FROM
            tongji
        GROUP BY
            author,
            month_video
    )
    -- 第三步：计算粉丝增长率 + 累计净增总粉丝
SELECT
    author,
    month,
    ROUND(net_fans_growth / view_count, 3) AS fans_growth_rate, -- 净增率 = 增长 / 播放量
    SUM(net_fans_growth) OVER (
        PARTITION BY
            author
        ORDER BY
            month 
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW
    ) AS fans_total_till_now -- 截止当前的累计净增粉丝
FROM
    monthly_growth
ORDER BY author ASC ,fans_total_till_now ASC;