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

select author, month, round(total_fans / open_total, 3), sum(total_fans) over(partition by author order by month) as total_fans
from (
select b.author, date_format(a.start_time, "%Y-%m") as month, 
count(1) as open_total, 
sum(case when if_follow = 1 then 1 
        when if_follow = 2 then -1 else 0 end) as total_fans
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where year(a.start_time) = 2021
group by b.author, date_format(a.start_time, "%Y-%m")
) t
group by author, month
order by author asc, total_fans asc