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

with t1 as (
    select a.uid, a.video_id, a.if_follow, b.author,  date_format(a.start_time,'%Y-%m-01') start_time
    from tb_user_video_log a join tb_video_info b on a.video_id = b.video_id
    where  date_format(a.start_time,'%Y') = 2021
), t2 as (
    select author, start_time, count(1) over(partition by author, start_time) total_user,
        case when if_follow = 1 then 1
             when if_follow = 2 then -1 else 0 end fan
    from t1
), t3 as (
    select distinct author, start_time, total_user, sum(fan) over(partition by author order by start_time) total_fans,
        sum(fan) over(partition by author , start_time) fan_up
    from t2 
), t4 as (
    select author, date_format(start_time,'%Y-%m'),  cast(fan_up/total_user as decimal(16,3)) fans_growth_rate,
        total_fans
    from t3
    order by author, total_fans

)
select * from t4