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

select author,date_format(start_time,'%Y-%m') dat,round(max(incre_fans)/count(*),3) zfl,max(total_fans) total
from(
select *
,sum(fans)over(partition by author,date_format(start_time,'%Y-%m') order by start_time asc) incre_fans
,sum(fans)over(partition by author order by start_time asc) total_fans
from(
select t2.author
,t1.start_time
,case when t1.if_follow=2 then -1
      else t1.if_follow end fans
from tb_user_video_log t1 join tb_video_info t2 on t1.video_id=t2.video_id
)as T1
)as T2
where date_format(start_time,'%Y')=2021
group by author,dat
order by author,total asc