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

       month,
       ROUND(fans_growth_rate, 3) AS fans_growth_rate,
       SUM(month_total_fans) OVER (PARTITION BY author ORDER BY month) AS total_fans 
FROM (     
    SELECT author,
           month,
           SUM( CASE if_follow
                WHEN 1 THEN 1
                WHEN 2 THEN -1
                END ) / COUNT(1) AS fans_growth_rate,
           SUM( CASE if_follow
                WHEN 1 THEN 1
                WHEN 2 THEN -1
                END ) AS month_total_fans
    FROM (
        SELECT a.author AS author,
               b.uid AS uid,
               if_follow,
               LEFT(b.start_time, 7) AS month
        FROM tb_video_info a
        LEFT JOIN tb_user_video_log b
        ON a.video_id = b.video_id
        WHERE start_time LIKE '2021%'
    ) AS temp1
    GROUP BY author, month
) temp2
ORDER BY author, total_fans