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

select author,
       date_format(end_time, '%Y-%m')                                         as month,
       round(sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end) / count(1),
             3)                                                               as fans_growth_rate,
       sum(sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end))
           over (partition by author order by date_format(end_time, '%Y-%m')) as total_fans
from tb_user_video_log tuvl
         join tb_video_info tvi on tuvl.video_id = tvi.video_id
where year(end_time) = 2021
group by author, month
order by author, total_fans;