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

SELECT author, month,
    ROUND(fans / play_cnt, 3) AS fans_growth_rate,
    SUM(fans) OVER(PARTITION BY author ORDER BY month) AS total_fans
FROM (
    SELECT author, 
        SUBSTRING(start_time, 1, 7) AS month,
        SUM(
            CASE 
            WHEN if_follow = 1 THEN 1
            WHEN if_follow = 0 THEN 0
            WHEN if_follow = 2 THEN -1
            END
        ) AS fans,
        COUNT(start_time) AS play_cnt
    FROM tb_user_video_log
        JOIN tb_video_info
        USING(video_id)
    WHERE YEAR(start_time) = '2021'
    GROUP BY author, month
)res_table
ORDER BY author, total_fans