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

# 先获取每个作者每个月的总播放量、涨粉数量、和累计粉丝数量
# 其中涨粉数量可以用if或者case when来进行处理
with a as (
select 
author 
,date_format(start_time,"%Y-%m") as month 
,sum(
    case
    when if_follow = "1" then 1 
    when if_follow = "2" then -1 
    else 0
    end
)  as fans_cnt 
,count(1) as play_cnt
from tb_user_video_log a 
inner 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") ) 

select 
author
,month
,round(fans_cnt/play_cnt,3) as 	fans_growth_rate 
,sum(fans_cnt) over(partition by author order by month) as total_fans 
from a 
order by author ,total_fans

注意审题，注意细节。。。