题解 | 统计创作者

with
    t as (
        select
            author_id,
            count(distinct title) posts_30d,
            sum(like_cnt) likes_30d
        from
            (
                select
                    author_id,
                    title,
                    like_cnt,
                    dense_rank() over (
                        partition by
                            author_id
                        order by
                            left(publish_ts, 7) desc
                    ) as ts_rank
                from
                    post
            ) tmp
        where
            ts_rank between 1 and 30
        group by
            tmp.author_id
    )
select
    a.author_id,
    a.author_name,
    posts_30d,
    likes_30d,
    ifnull(round(likes_30d / posts_30d, 2), 0) avg_likes_30d
from
    author a
    left join t using (author_id)
where
    posts_30d > 0
order by
    likes_30d desc,
    posts_30d desc,
    a.author_id
limit
    5