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

-- 需求：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 
-- 拆解思路如下：
-- 1、以互动表为左表，使用left join以video_id为关联条件，获取信息表中的用户
-- 2、使用left函数获取日期对应的月份 
-- 3、使用case when函数对if_follow的不同结果进行赋值，再使用sum和count分别计算对应的粉丝量、播放量来计算对应的涨粉率
-- 4、需要用sum窗口函数来统计截止当月的粉丝量。因为语句中有group by，所以需要先根据group by语句sum每个月的粉丝数，然后再使用开窗函数对月进行累计

select 
author 
,left(start_time,7) as month 
,round(sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end)/count(start_time),3) as fans_growth_rate 
,sum(sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end)) over (partition by author order by left(start_time,7)) as total_fans 
from tb_user_video_log t1 
left join tb_video_info t2 
on t1.video_id = t2.video_id 
where year(start_time) = 2021 
group by author ,month 
order by author ,total_fans