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

SELECT author
        ,month
        ,fans_growth_rate
        ,SUM(fans) OVER(PARTITION BY author ORDER BY month) AS total_fans
FROM(
    SELECT author
            ,DATE_FORMAT(end_time, "%Y-%m") AS month
            ,ROUND((SUM(IF(if_follow = 1,1 ,0))-SUM(IF(if_follow = 2,1 ,0)))/COUNT(start_time),3) AS fans_growth_rate
            ,SUM(IF(if_follow = 1,1 ,0))-SUM(IF(if_follow = 2,1 ,0)) AS fans
    FROM tb_video_info
    JOIN tb_user_video_log USING(video_id)
    WHERE YEAR(start_time) = 2021
    GROUP BY author, DATE_FORMAT(end_time, "%Y-%m")
) data1
ORDER BY author,total_fans