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

SELECT
    author,
    DATE_FORMAT(start_time,"%Y-%m") `month`,
    ROUND(SUM(IF(if_follow=2,-1,if_follow))/COUNT(*),3) fans_growth_rate,
    SUM(SUM(IF(if_follow=2,-1,if_follow))) 
        OVER(PARTITION BY author ORDER BY DATE_FORMAT(start_time,"%Y-%m")) total_fans
FROM
    tb_user_video_log uvl
INNER JOIN
    tb_video_info vi
    ON uvl.video_id = vi.video_id
WHERE
    YEAR(start_time) = 2021
GROUP BY
    author,
    `month`
ORDER BY
    author,total_fans