题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
with t as(select
author,
substr(end_time,1,7) as yue,
if(if_follow = 2,-1,if_follow) as follow
from tb_video_info v
join tb_user_video_log u on v.video_id = u.video_id
where year(start_time)=2021),
q as
(select
author,
yue,
round(sum(if(follow <> 1,follow,1))/count(1),3) as rate,
sum(follow) as fan
from t
group by author,yue)
select
author,
yue,
rate,
sum(fan) over(partition by author order by yue rows between unbounded preceding and current row) as total_fans
from q
order by author,total_fans