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

with
    t1 as (
        select
            a.video_id vid,
            mth,
            fans_flag,
            author
        from
            (
                select
                    video_id,
                    DATE_FORMAT (end_time, "%Y-%m") mth,
                    case
                        when if_follow = 1 then 1
                        when if_follow = 0 then 0
                        when if_follow = 2 then -1
                    end as fans_flag
                from
                    tb_user_video_log
            ) as a
            left join (
                select
                    video_id,
                    author
                from
                    tb_video_info
            ) as b on a.video_id = b.video_id
    )
# 计算总播放率：count(vid) -> 每一条对应一次播放
# 涨粉：sum(fans_flag) -> 正负抵消
select
    author, mth, round(sum(fans_flag) / count(vid),3) fans_growth_rate, (
        select sum(fans_flag)
        # 使用关联子查询时，将这里的表记得重命名
        from t1 as help
        # 关联子查询
        where help.author = t1.author and help.mth <= t1.mth
    ) total_fans
from
    t1
# 日期字符串可以直接比较
where mth >= '2021-01'
group by author, mth
order by author, total_fans