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

select t1.auth author, t1.da, round(t1.retweet_cnt/cnt,3), sum(t1.retweet_cnt) over(partition by t1.auth order by t1.da) total_fans from ( select ti.author auth, date_format(tl.end_time,'%Y-%m') da, sum(if(if_follow = 2,-1,if_follow)) retweet_cnt, count(*) cnt from tb_user_video_log tl join tb_video_info ti on tl.video_id = ti.video_id where year(tl.start_time) = 2021 group by ti.author,da ) t1 order by author,total_fans