题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
-- 计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 -- 涨粉率=(加粉量 - 掉粉量) / 播放量 -- 计算涨粉率和总粉丝数量 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 ( -- 1、先获取每个作者每个月的播放量、涨粉量 select author ,left(start_time,7) as month ,count(1) as play_cnt ,sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end ) as fans_cnt from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id where year(start_time) = '2021' group by author ,left(start_time,7) ) c order by author ,total_fans