题解 | 统计创作者

with base as (
    select max(publish_ts) as max_ts
    from post
)

select
    a.author_id,
    a.author_name,
    count(p.post_id) as posts_30d,
    sum(p.like_cnt) as likes_30d,
    round(sum(p.like_cnt) / count(p.post_id), 2) as avg_likes_30d
from
    author a
join
    post p on a.author_id = p.author_id
join
    base b
where
    timestampdiff(day, p.publish_ts, b.max_ts) <= 30
group by
    a.author_id, a.author_name
having
    count(p.post_id) > 0
order by
    likes_30d desc,
    posts_30d desc,
    a.author_id asc
limit 5;