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

with
t1 as(
    select
        i.author,
        date_format(p.start_time,'%Y-%m') as month,
        case 
            when p.if_follow=1 then 1
            when p.if_follow=2 then -1
            else 0 end as fans_num
    from tb_video_info i left join tb_user_video_log p using(video_id)
    where left(p.start_time,4)='2021'
    order by author,month),
t2 as(
    select
        author,
        month,
        ifnull(round(sum(fans_num)/count(*),3),0.000) as fans_growth_rate,
        sum(fans_num) as month_fans_num
    from t1
    group by author,month
    order by author,month)

select
    author,
    month,
    fans_growth_rate,
    sum(month_fans_num) over(partition by author rows between unbounded preceding and current row) as total_fans
from t2
order by author,total_fans