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

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