题解 | 统计创作者

WITH tb1 AS(
SELECT author.author_id, 
        author.author_name, 
        COUNT(post.post_id) AS posts_30d,
        SUM(like_cnt) AS likes_30d
FROM post
LEFT JOIN author ON post.author_id = author.author_id
GROUP BY post.author_id
),
tb2 AS(
SELECT author_id,
       author_name,
       posts_30d,
       likes_30d,
       ROUND(likes_30d / posts_30d, 2) AS avg_likes_30d,
       RANK() OVER(ORDER BY posts_30d DESC) AS rk
FROM tb1
ORDER BY likes_30d DESC, posts_30d DESC, author_id ASC
)

SELECT author_id,
       author_name,
       posts_30d,
       likes_30d,
       avg_likes_30d
FROM tb2
WHERE rk < 3