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

-- 涨粉率=(加粉量 - 掉粉量) / 播放量
-- order by uid, 总粉丝量
-- if_follow = 1 加粉丝

with t as (select log.uid, log.video_id, log.start_time, log.if_follow,
 info.author
from tb_user_video_log log left join tb_video_info info
on log.video_id = info.video_id
where year(log.start_time) = 2021),

t2 as (select author, substring(start_time, 1, 7) as month,
(sum(case when if_follow = 1 then 1 else 0 end ) -
sum(case when if_follow = 2 then 1 else 0 end)) / count(uid)
as fans_growth_rate,
(sum(case when if_follow = 1 then 1 else 0 end ) -
sum(case when if_follow = 2 then 1 else 0 end)) as net_addfans
from t
group by author, month)

select author, month, round (fans_growth_rate,3),
sum(net_addfans) over (partition by author order by month) as total_fans
from t2 order by author, total_fans