题解 | 每个创作者每月的涨粉率及截止当前的总粉丝量
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
select author,month,fans_growth_rate,sum(fans)over(partition by author order by month) total_fans from( select author,left(start_time,7) as month,round((sum(if(if_follow = 1,1,0))-sum(if(if_follow = 2,1,0)))/count(1),3) fans_growth_rate,sum(if(if_follow = 1,1,0))-sum(if(if_follow = 2,1,0)) as fans from tb_user_video_log tuvl join tb_video_info tvi on tuvl.video_id = tvi.video_id where left(start_time,4) = '2021' group by author,left(start_time,7)) as t1 order by author,total_fans