题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
SELECT author, date_format(start_time,'%Y-%m') month, round(sum(if(if_follow=2,-1,if_follow)) / count(*),3) as 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 log left join tb_video_info info on log.video_id=info.video_id where year(start_time)=2021 group by author,month order by author,total_fans