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

select author,month,fans_growth_rate,
sum(follow) over(partition by author order by month) as total_fans from(
select author,left(start_time,7) month,round(sum(
case when if_follow=2 then -1 else if_follow end)/count(*),3) as fans_growth_rate,
sum(case when if_follow=2 then -1 else if_follow end) as follow
from tb_user_video_log t1,tb_video_info t2
where t1.video_id=t2.video_id and year(start_time)=2021
group by t2.author,month
)a order by a.author,total_fans