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

select author,t1.month,
round((growth_cnt-drop_cnt)/play_cnt,3) fans_growth_rate,
sum(total)over(partition by author order by t1.month) total_fans
from
(
select author,date_format(start_time,'%Y-%m') as month,
count(a.video_id) play_cnt,
sum(if(if_follow = 2, 1 ,0)) drop_cnt,
sum(if(if_follow = 1, 1 ,0)) growth_cnt,
sum(if(if_follow = 2, -1 ,if_follow )) as total
from tb_user_video_log a
left join tb_video_info  b on a.video_id = b.video_id
where year(start_time) = 2021
group by author,date_format(start_time,'%Y-%m')
)t1
order by author, total_fans