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

with t1 as (select author,
                   substr(end_time, 1, 7)            as mon,
                   count(if(if_follow = 1, 1, null)) as addfensi,
                   count(if(if_follow = 2, 1, null)) as subfensi,
                   count(1)                          as tol
            from tb_user_video_log t1
                     join tb_video_info t2
                          on t1.video_id = t2.video_id
            where substr(end_time, 1, 4) = 2021
            group by substr(end_time, 1, 7), author)
select author,
       mon,
       round((addfensi - subfensi) / tol, 3)  as fans_growth_rate,
       sum(addfensi - subfensi)
           over (partition by author order by mon,addfensi,subfensi  ) as total_fans
from t1 order by author,total_fans;