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

select author,month,fans_growth_rate,
    sum(total_fans) over(partition by author order by month) as total_fans
from (select author,month,
    round(sum(follows)/count(author),3) as fans_growth_rate,
    sum(follows) as total_fans
from(
    select author,substring_index(start_time,'-',2) as month,
        if(if_follow='2',-1,if_follow) as follows
    from tb_user_video_log u
    left join tb_video_info v
    on u.video_id=v.video_id
    where start_time like '2021%'
    ) as uv
group by author,month
) as t
order by author asc,total_fans asc