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

select * from
    (select author,
    date_format(start_time,'%Y-%m') month, 
    round((sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0)))/count(1),3) fans_growth_rate, 
    sum(sum(case if_follow when 1 then 1 when 2 then -1 end)) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans
    from tb_user_video_log u 
    inner join tb_video_info v 
    on u.video_id=v.video_id 
    group by author,date_format(start_time,'%Y-%m')
    order by author,total_fans)t
where month between '2021-01' and '2021-12'