トップレベルカテゴリ別およびユーザー別の投稿数
各トップレベルカテゴリについて、そのカテゴリに貢献したユーザーのリストと、各ユーザーが投稿した投稿数を表示します。投稿にはトピック作成者の投稿と、その下にあるコメントの両方が含まれます。クエリを編集することで、特定のトップレベルカテゴリのみを表示することもできます。
/* サブカテゴリおよびトップレベルカテゴリのコードを合計 */
SELECT
category_id, username AS "ユーザー", SUM(contributions) AS "投稿数"
FROM (
/* トップレベルカテゴリにトピックを持つユーザー */
SELECT
topics.category_id, COUNT(*) AS contributions, users.username
FROM users
INNER JOIN posts ON users.id = posts.user_id
INNER JOIN topics ON posts.topic_id = topics.id
INNER JOIN categories ON topics.category_id = categories.id
WHERE categories.parent_category_id IS NULL
GROUP BY category_id, username
UNION
/* サブカテゴリにトピックを持つユーザー（トップレベルカテゴリ別） */
SELECT
categories.parent_category_id AS category_id, COUNT(*) AS contributions, users.username
FROM users
INNER JOIN posts ON users.id = posts.user_id
INNER JOIN topics ON posts.topic_id = topics.id
INNER JOIN categories ON topics.category_id = categories.id
WHERE categories.parent_category_id IS NOT NULL
GROUP BY categories.parent_category_id, username
) AS users_by_category
INNER JOIN categories ON category_id = categories.id
GROUP BY category_id, username
HAVING SUM(contributions) > 5
ORDER BY category_id, "投稿数" DESC