题解 | 统计创作者

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

WHERE DATEDIFF((SELECT MAX(publish_ts)FROM post), publish_ts) BETWEEN 0 AND 29

筛选出 “最新帖子发布日（基准日）往前推 0~29 天” 内发布的帖子