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

WITH t1 AS(
SELECT author,
DATE_FORMAT(start_time,'%Y-%m') AS month,
SUM(CASE WHEN if_follow = 1 THEN 1 
         WHEN if_follow = 2 THEN -1
         ELSE 0 END) AS add_fans,
COUNT(start_time) AS play_nums
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
USING(video_id)
WHERE YEAR(start_time) = '2021'
GROUP BY 1,2
)
SELECT author,
month,
ROUND(add_fans/play_nums,3) AS fans_growth_rate,
SUM(add_fans)OVER(PARTITION BY author ORDER BY month) AS total_fans
FROM t1
ORDER BY 1,4