题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
select b.author,
date_format(a.start_time,'%Y-%m') as month,
round(sum(case when a.if_follow=2 then -1
else a.if_follow end)/count(1),3) as fans_growth_rate,
sum(sum(case when a.if_follow=2 then -1
else a.if_follow end)) over (partition by b.author order by date_format(a.start_time,'%Y-%m')) as total_fans
from tb_user_video_log a
inner join tb_video_info b
on a.video_id=b.video_id
where YEAR(a.start_time)='2021'
group by b.author,month
order by b.author asc,total_fans asc
查看13道真题和解析