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

with tb as (
    select author, date_format(start_time, "%Y-%m") as month,
    sum(case when if_follow = 2 then -1 else if_follow end) / count(*) as fans_growth_rate, 
    sum(case when if_follow = 2 then -1 else if_follow end) as fans_this_month
    from tb_user_video_log uvl
    left outer join tb_video_info vi
    on uvl.video_id = vi.video_id
    where year(start_time) = 2021
    group by author, month
    order by author, month
)
select author, month, round(fans_growth_rate, 3) as fans_growth_rate, sum(fans_this_month) over(partition by author order by month) as total_fans
from tb
order by author, total_fans;

这次被卡在累加一列了，窗口函数可以干这个。

以及我只能在CTE里加年份条件，加在外面 (year(month) = 2021) 跑不了。