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

该题就难在计算总粉丝数

SELECT
    author,
    month,
    ROUND(fans_cnt / BClick, 3) AS fans_growth_rate,
    SUM(fans_cnt) over (
        partition by
            author
        order by
            month
    ) as total
FROM
    (
        SELECT
            author,
            month,
            SUM(if (if_follow = 2, -1, if_follow)) AS fans_cnt,
            COUNT(video_id) AS BClick
        FROM
            (
                SELECT
                    author,
                    DATE_FORMAT (start_time, "%Y-%m") AS month,
                    if_follow,
                    video_id
                FROM
                    tb_user_video_log
                    LEFT JOIN tb_video_info USING (video_id)
                WHERE
                    YEAR (start_time) = 2021
            ) AS T1
        GROUP BY
            author,
            month
    ) AS T2
    ORDER BY author,total;