Haven’t seen these cases covered in this topic, so here are my queries:
Number of posts by top-level category and user
For each top-level category, a list of the users who contributed to it and the number of posts they contributed. Posts include both the topic-starter posts and the comments below. You can select to see only one top-level category by editing the query.
/* Sum up codes in sub-level and top-level categories */
SELECT
category_id, username AS "User", SUM(contributions) AS "Post Count"
FROM (
/* Users with topics in top-level categories. */
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
/* Users with topics in sub-level categories, by top-level category. */
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, "Post Count" DESC
Views by top-level category
Including grand total of all topic views on your forum since the very beginning.
/* Outer SELECT to hide the artificial sort order column, and sort by it. */
SELECT "Category", "Views"
FROM (
/* Views per top-level category. */
SELECT
topcat AS "Category",
SUM(views) AS "Views",
0 AS sortorder /* Artificial sorting column, here to sort to the beginning. */
FROM (
/* Topic views in sub-level categories. */
SELECT
topcat.name AS topcat, SUM(topics.views) AS views
FROM topics
INNER JOIN categories subcat ON topics.category_id = subcat.id
INNER JOIN categories topcat ON subcat.parent_category_id = topcat.id
GROUP BY topcat.name
UNION
/* Topic views in top-level categories (excluding sub-level cats). */
SELECT
topcat.name AS topcat, SUM(topics.views) AS views
FROM topics
INNER JOIN categories topcat ON topics.category_id = topcat.id
WHERE topcat.parent_category_id IS NULL
GROUP BY topcat.name
) AS views_by_cat
GROUP BY topcat
UNION
/* Adding a TOTAL row at the end. */
SELECT
'GRAND TOTAL' AS "Category",
SUM(topics.views) AS "Views",
1 AS sortorder /* Artificial sorting column, here to sort to the end. */
FROM
topics
GROUP BY "Category"
/* Sort the output by either category or views. Enable one of these: */
/* ORDER BY topcat, subcat */
ORDER BY "Views" DESC
) AS views_by_cat_with_total
ORDER BY sortorder, "Category"
Views by sub-level category
A more granular analysis of topic views, but without summing them up. Entries with sub-category “(none)” relate to topics placed directly into the top-level categories.
SELECT
topcat AS "Category",
subcat AS "Subcategory",
SUM(views) AS "Subcategory views"
FROM (
/* Topic views in sub-level categories. */
SELECT
topcat.name AS topcat, subcat.name AS subcat, SUM(topics.views) AS views
FROM topics
INNER JOIN categories subcat ON topics.category_id = subcat.id
INNER JOIN categories topcat ON subcat.parent_category_id = topcat.id
GROUP BY topcat.name, subcat.name
UNION
/* Topic views in top-level categories (excluding sub-level cats). */
SELECT
topcat.name AS topcat, '(none)', SUM(topics.views) AS views
FROM topics
INNER JOIN categories topcat ON topics.category_id = topcat.id
WHERE topcat.parent_category_id IS NULL
GROUP BY topcat.name
) AS views_by_cat
GROUP BY topcat, subcat
/* Sort the output by either category or views. Enable one of these: */
/* ORDER BY topcat, subcat */
ORDER BY "Subcategory views" DESC
