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

WITH
    t1 AS (
        SELECT
            video_id,
            date_format(start_time,'%Y-%m') end_month,
            YEAR (end_time) AS year_time,
            CASE
                WHEN if_follow = 1 THEN 1
                WHEN if_follow = 2 THEN -1
                else 0
            END as if_follow
        FROM
            tb_user_video_log
        WHERE YEAR (end_time) = 2021
    )
SELECT
    vin.author,
    uv.end_month,
    round(Sum(uv.if_follow)/count(1),3) AS fans_growth_rate,
    sum(SUM(uv.if_follow)) OVER (
        PARTITION BY
            vin.author
        ORDER BY
            
            uv.end_month
    ) AS total_fans
FROM
    t1 uv
    LEFT JOIN tb_video_info vin ON uv.video_id = vin.video_id
WHERE
    uv.year_time = 2021
GROUP BY
    vin.author,
    uv.end_month
order by 
    vin.author,
    total_fans