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

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?

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?

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). :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.