Hi!
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?
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?
2 Likes
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 */
SELECT
category_id, SUM(contributions) AS posts_count, username
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
/* 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 categories.name = 'Campfire' */
/* WHERE categories.name = 'Wellbeing in Europe' */
/* WHERE categories.name = 'Internet of Humans' */
/* WHERE categories.name = 'Culture Squad' */
/* WHERE categories.name = 'Research Network' */
/* WHERE categories.name = 'EarthOS' */
/* WHERE categories.name = 'Participio' */
/* WHERE categories.name = 'OCI Lab' */
/* WHERE categories.name = 'Documentation & Support' */
/* WHERE categories.name = 'Knowledge Collection' */
/* WHERE categories.name = 'Workspaces' */
/* WHERE categories.name = 'Uncategorized' */
GROUP BY category_id, username
HAVING SUM(contributions) > 5
ORDER BY category_id, posts_count DESC
3 Likes
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,
CCS
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). 
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…
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