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

select
author
,month
,round((ifo - info)/cnt,3) fans_growth_rate
,sum(ifo)over(partition by author order by month) - sum(info)over(partition by author order by month) total_fans
from
(
    select
    author
    ,date_format(start_time,'%Y-%m') month
    ,sum(if(if_follow = 1,1,0)) ifo#当月涨粉数
    ,sum(if(if_follow = 2,1,0)) info#当月掉粉数
    ,count(start_time) cnt#当月观看数量
    from tb_user_video_log tu left join tb_video_info tv  
    on tu.video_id = tv.video_id
    where year(start_time) = 2021
    group by 1,2
)table1
order by 1,4