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

select author,month,
       round((follow_cn - unfollow_cn)/play_times,3) as fans_growth_rate,
       sum(month_total_fans)over(partition by author order by month) as total_fans
from (
    select author,date_format(start_time,'%Y-%m') as month,
           sum(if(if_follow=1,1,0)) as follow_cn,
           sum(if(if_follow=2,1,0)) as unfollow_cn,
           count(1)  as play_times,
           sum(if(if_follow=1,1,0)) - sum(if(if_follow=2,1,0)) month_total_fans
    from tb_user_video_log t1
    join tb_video_info t2
    on t1.video_id = t2.video_id
    where year(start_time) = 2021
    group by author,date_format(start_time,'%Y-%m')
    ) t
order by author,total_fans