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

with a as(
    select
        tvi.author,
        date_format(tuvl.start_time,'%Y-%m') as dt,
        case when tuvl.if_follow=0 then 0 when tuvl.if_follow = 1 then 1 when tuvl.if_follow = 2 then -1 end as fans_bianhua
    from 
        tb_user_video_log as tuvl 
        left join tb_video_info as tvi on tuvl.video_id = tvi.video_id
    where
        year(tuvl.start_time) = 2021
),
b as(
    select
        author,
        dt,
        round(sum(fans_bianhua)/count(*),3) as fans_growth_rate,
        sum(fans_bianhua) as fans_month_bianhua
    from 
        a
    group by 
        author,
        dt
),
c as(
    select 
        author,
        dt as month,
        fans_growth_rate,
        sum(fans_month_bianhua)over(partition by author order by dt) as total_fans
    from 
        b
    order by 
        author,
        total_fans
)
select * from c