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

SELECT author, month, 
    ROUND(SUM(if_follow)/COUNt(DISTINCT uid),3) fans_growth_rate,
    SUM(SUM(if_follow)) OVER (PARTITION BY author ORDER BY month) total_fans 
FROM(
    SELECT tv.author,tu.month, tv.video_id, tu.uid, if_follow
    FROM(
        SELECT uid, video_id, DATE_FORMAT(start_time,'%Y-%m') month,  
        CASE WHEN if_follow = 2 THEN -1 ELSE if_follow END if_follow
        FROM tb_user_video_log
    ) tu
    JOIN  tb_video_info tv USING(video_id)
    WHERE LEFT(tu.month,4) = '2021'
) a 
GROUP BY author, month
ORDER BY author,total_fans