select p.author_id, a.author_name, count(*) as posts_30d, sum(p.like_cnt) as likes_30d, (case when count(*) = 0 then 0 else round(sum(p.like_cnt)/count(*),2) end) as avg_likes_30d
from author a
join post p on a.author_id = p.author_id
where datediff((select date_format(max(post.publish_ts), '%Y-%m-%d') FROM post), date_format(p.publish_ts, '%Y-%m-%d')) <= 30
group by p.author_id, a.author_name
having count(*) > 0
order by likes_30d desc,posts_30d desc, author_id asc
limit 5