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

WITH
zb AS (             -- 清洗数据，得出2021年内的月份，粉丝变化明细，作者
    SELECT
        DATE_FORMAT(u.start_time,'%Y-%m') AS month,
        u.if_follow,
        v.author
    FROM tb_user_video_log AS u
    INNER JOIN tb_video_info AS v
        ON (u.video_id = v.video_id)
    WHERE u.start_time >= '2021-01-01 00:00:00'
        AND u.start_time < '2022-01-01 00:00:00'
),

tf AS (             -- 对作者和月份分组，计算视频播放量，计算粉丝增长数。
    SELECT
        author,month,
        COUNT(*) AS gk,
        SUM(CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN -1 ELSE 0 END) AS fs
    FROM zb
    GROUP BY author,month
)

SELECT              -- 主查询
    author,month,
    ROUND(fs/gk,3) AS fans_growth_rate,             -- 粉丝增长/播放量=粉丝增长率
    SUM(fs) OVER (PARTITION BY author ORDER BY month) AS total_fans             -- 开窗口函数，按作者分组，对月份升序后累加粉丝数
FROM tf
ORDER BY author,total_fans;