题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
SELECT author , DATE_FORMAT(start_time,'%Y-%m') AS month , ROUND(SUM(CASE if_follow WHEN 1 THEN 1 WHEN 2 THEN -1 ELSE 0 END) / COUNT(start_time),3) AS fans_growth_rate #计算出每个月涨粉率，用CASE WHEN把2转换成-1，然后相加就计算出了每月粉丝量，count(start_time)计算出每月播放量，不会计算NULL值 , SUM(SUM(CASE if_follow WHEN 1 THEN 1 WHEN 2 THEN -1 ELSE 0 END)) OVER (PARTITION BY author ORDER BY DATE_FORMAT(start_time,'%Y-%m')) AS total_fans #用sum()over()计算出每人每月总粉丝量，这里可以用到上面的每月粉丝量 FROM tb_user_video_log JOIN tb_video_info USING (video_id) WHERE YEAR(start_time) = '2021' GROUP BY author, DATE_FORMAT(start_time,'%Y-%m') ORDER BY author, total_fans;
，
查看19道真题和解析
