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

select author, month,  
round(fans_add_cnt /play_cnt, 3) as fans_growth_rate,
    sum(fans_add_cnt) 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=2, -1, if_follow)) as fans_add_cnt,count(1) as play_cnt
    from tb_user_video_log
    join tb_video_info USING(video_id)
    where YEAR(start_time) = 2021
    group by author, month) as t3
order by author, total_fans;