按顶级分类和用户划分的帖子数量

社区 数据与报告
1

顶级分类与用户发布的帖子数量

针对每个顶级分类，列出贡献该分类的用户及其发布的帖子数量。帖子包括主题发起帖和下方的回复。您可以通过编辑查询来仅查看某一个顶级分类。

/* 汇总子级和顶级分类中的代码 */
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