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

with a as
(
    select author, date_format(start_time,'%Y-%m') as month,
        sum(
            case when if_follow=1 then 1 
            when if_follow=2 then -1
            else 0
            end
            ) fans_num,
        count(*) as play_amount
    from tb_video_info v
    join tb_user_video_log u
    on u.video_id = v.video_id
    where year(start_time)=2021
    group by author,month
)

select author, month,
    round(fans_num/play_amount,3) as fans_rate,
    sum(fans_num) over(partition by author order by month) as total_fans
from a
order by author, total_fans