Number of posts by top-level category and user

data & reporting
1

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