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

select
    author,
    `month`,
    round((count(if(if_follow=1,1,null))- count(if(if_follow=2,1,null))) / count(look_time),3) 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 month) total_fans
from 
(
    select
        t1.uid,
        t1.video_id,
        t1.if_follow,
        t2.author,
        timestampdiff(second,t1.start_time,t1.end_time) look_time,
        substring(t1.end_time,1,7) as month
    from tb_user_video_log t1
    join tb_video_info t2 on t1.video_id = t2.video_id
    where year(t1.end_time) = 2021
)t
group by author,month
order by author,total_fans