题解 | 统计创作者

select 
    a.author_id, a.author_name, 
    count(*) posts_30d,
    sum(like_cnt) likes_30d,
    round(ifnull(sum(like_cnt)/count(*),0),2) avg_likes_30d
from 
    post p join author a
on 
    p.author_id = a.author_id
where 
    datediff((select max(publish_ts) from post), publish_ts) between 0 and 29
group by 
    a.author_id
order by 
    likes_30d desc, posts_30d desc, a.author_id
limit 
    5

需要计算posts数量（count），然后加和like（sum），计算平均喜爱度的话记得使用ifnull

计算最新的发布帖子近30天的话，选择max（发型帖子）然后使用datediff