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

select author,mon,round((add_fan-sub_fan)/move,3) as rate, fan_amount
from(
    select i.author,date_format(start_time,'%Y-%m')as mon
    ,sum(if(if_follow=1,1,0))as add_fan,sum(if(if_follow=2,1,0))as sub_fan
    ,count(start_time) as move
    ,sum(SUM(case when if_follow=1 then 1 
                  when if_follow=2 then -1
                  else 0 end)
        ) over(partition by i.author order by date_format(start_time,'%Y-%m') ) as fan_amount
    from tb_user_video_log t 
    left join tb_video_info i on t.video_id = i.video_id
    where year(t.start_time)= 2021
    group by i.author,mon
)a
order by author,fan_amount