How to get list of people who have contributed to a category?

I would like to reach out to the different groups of people who participated in different categories and subcategories. Is there some way to easily generate the list of people who contributed to a category or subcategory?

Not by us moderators afaik.
@matthias helped me with this once…

We have now the Data Explorer plugin that allows us to run queries that would return what you are looking for (needs being a Discourse admin user, so e-mail addresses etc. are still protected this way).

This needs creating the queries once, which I can do (given a bit of time). When would you need this?


When is a reasonable time frame? Nice to have soon, but not as cost of stress.

Let’s give it a week. It’s not that much work, so I can probably fit it in earlier.

1 Like

Ok, here’s your data explorer query to see users and their post counts per top-level category. Just click the link and then Run to see the results.

Any post directly in the top-level category or in any of its sub-categories is counted as a contribution for the top-level category this query. By default, users who contributed 5 posts or less are excluded, but you can easily adapt this in the HAVING clause near the bottom. You can also restrict the output to one top-level category only as per the instructions. This is useful in case there are more than 1000 results, in which case not all of them would be shown.

Backup of that query, also in case anyone else from the Interwebs finds it useful:

/* Sum up codes in sub-level and top-level categories */
    category_id, SUM(contributions) AS posts_count, username

    /* Users with topics in top-level categories. */
        topics.category_id, COUNT(*) AS contributions, users.username
    FROM users 
        INNER JOIN posts ON = posts.user_id
        INNER JOIN topics ON posts.topic_id =
        INNER JOIN categories ON topics.category_id =
    WHERE categories.parent_category_id IS NULL
    GROUP BY category_id, username
    /* Users with topics in sub-level categories, by top-level category. */
        categories.parent_category_id AS category_id, COUNT(*) AS contributions, users.username
    FROM users 
        INNER JOIN posts ON = posts.user_id
        INNER JOIN topics ON posts.topic_id =
        INNER JOIN categories ON topics.category_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 =

/* To see results for one category only, enable exactly one of the 
   lines below by removing the comment markers at the beginning and end. 
   You can then use "HAVING SUM(contributions) > 1" below as including 
   rarely contributing users will then not exceed the max. results. */

/* WHERE = 'Campfire' */
/* WHERE = 'Wellbeing in Europe' */
/* WHERE = 'Internet of Humans' */
/* WHERE = 'Culture Squad' */
/* WHERE = 'Research Network' */
/* WHERE = 'EarthOS' */
/* WHERE = 'Participio' */
/* WHERE = 'OCI Lab' */
/* WHERE = 'Documentation & Support' */
/* WHERE = 'Knowledge Collection' */
/* WHERE = 'Workspaces' */
/* WHERE = 'Uncategorized' */

GROUP BY category_id, username

HAVING SUM(contributions) > 5

ORDER BY category_id, posts_count DESC

Thank you Matt @owen this is going to come in handy soon! @noemi @johncoate @alberto @MariaEuler @amelia @Leonie @CCS you may find this helpful too

Ohh thank you!!

Hi @matthias, thanks for this! I was wondering if you have a github repository of this code? I work with some people on a software for mailinglist analysis called BigBang and we are doing very similar things. Would be cool to link up!

Kind regards,


Hmm no, it’s not on Github. If I’d post it there, it would go into this list.

This is code that’s only useful within the Discourse forum software that we use, relying on a plugin for it called Data Explorer. It’s not immediately useful to your mailing list analysis software.

If you want the code, take it. I hereby make it public domain (CC0). :slight_smile:

1 Like

Hallo Matthias, does this include all the users who have contributed in the subcategories to a category also?

Yes, contributions to a sub-level category are counted towards the containing top-level category. I updated the description above and the one in Data Explorer accordingly.

Hi @matthias, is it possible to get the list with ONLY contributors to a subcategory?
Thinking about Wellbeing in Europe, where subcommunities are very strong and looking at the big list we cannot identify who;s in the PL or CZ language group…

Just checking again if this is possible @matthias?

AFAIK It is possible, you just use the same script but pointing to a subcategory.

This is possible now, with the new query “Posts: by user, for one category”. It is also accessible to moderators using this link.

This new query can list users and post counts for any category, whether top-level or sub-level. This means, adapting the original query “Posts: by top-level category and user” to limit it to one top-level category is no longer needed, so I removed that part from the original query (cc @nadia, who will have to use the new query for these purposes now).

1 Like

Thanks Matt, it works!!

1 Like