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

# 找到2021年每个月哥哥视频的粉丝变化量以及是视频播放量
with t1 as (
    select video_id, count(case when if_follow = 1 then uid end) as fan_add,
    count(case when if_follow = 2 then uid end) as fan_sub,
    count(uid) as watch_cnt,
    date_format(start_time, '%Y-%m') as month
    from tb_user_video_log
    where start_time between '2021-01-01 00:00:00'and '2021-12-31 23:59:59'
    group by date_format(start_time, '%Y-%m') , video_id
),# 计算每个作者每个月的涨粉率
t2 as (
    select a.author, t1.month, 
    round((sum(t1.fan_add)-sum(t1.fan_sub))/sum(t1.watch_cnt), 3) as fans_growth_rate
    from t1
    left join tb_video_info as a
    on a.video_id = t1.video_id
    group by a.author, t1.month
),# 计算每个月视频创作者的粉丝变化量
t3 as (
    select b.author, c.month, sum(c.fan_change) as fan_change
    from tb_video_info as b
    right join (
        select video_id, date_format(start_time, '%Y-%m') as month, 
        sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 end) as fan_change
        from tb_user_video_log
        where start_time between '2021-01-01 00:00:00'and '2021-12-31 23:59:59'
        group by video_id, date_format(start_time, '%Y-%m')
    ) as c
    on c.video_id = b.video_id
    group by b.author, c.month
),# 计算每个创作者每个月的粉丝总量
t4 as (
    select author, month, 
    sum(fan_change) over(partition by author order by month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as total_fans
    from t3
)
# 得到答案
select t4.author, t4.month, t2.fans_growth_rate, t4.total_fans
from t2
left join t4
on t2.month= t4.month
and t2.author = t4.author
order by t4.author, t4.total_fans;