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

SELECT
    author
    ,m_onth AS month
    ,ROUND(follows/ct,3) AS fans_growth_rate
    ,SUM(follows)OVER(PARTITION BY author ORDER BY m_onth) AS total_fans
FROM
(
    SELECT
        t2.author
        ,LEFT(t1.start_time,7) AS m_onth
        ,SUM(CASE if_follow WHEN 1 THEN 1 WHEN 0 THEN 0 WHEN 2 THEN -1 END) AS follows
        ,COUNT(t1.video_id) as ct  
    FROM tb_user_video_log AS t1 LEFT JOIN tb_video_info AS t2
    ON t1.video_id=t2.video_id
    GROUP BY
        t2.author
        ,m_onth
) AS t1
WHERE LEFT(m_onth,4)=2021
ORDER BY author,total_fans