题解 | 统计创作者

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) / nullif(count(p.post_id), 0), 2) as avg_likes_30d
from
    author a join post p on a.author_id = p.author_id
where
    p.publish_ts >= 
        (select date_sub(max(publish_ts), interval 30 day) from post)
group by 
    a.author_id, a.author_name
order by
    likes_30d desc, posts_30d desc, a.author_id asc
limit
    0, 5;