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

with
t1 as (select video_id,substring(start_time,1,7) months,if_follow from tb_user_video_log where YEAR(start_time)=2021),
t2 as (select t1.*,tb.author,count(if_follow) over(partition by t1.months,tb.author) cnt from t1 inner join tb_video_info tb on t1.video_id=tb.video_id),
t3 as (select distinct author,months,cnt,sum(case if_follow when 1 then 1 when 2 then -1 else 0 end) over(partition by author order by months) kk from t2),
t4 as (select author,months,round((kk-LAG(kk,1,0)over(partition by author))/cnt,3) pp,kk from t3),
t5 as (select author,months,pp,kk,row_number() over(partition by author order by kk) from t4 )
select author,months,pp,kk from t5