# See what categories new users (& other trust levels) post in most

**URL:** <https://meta.discourse.org/t/see-what-categories-new-users-other-trust-levels-post-in-most/80494>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [13 februari 2018 om 19:49 UTC](https://meta.discourse.org/t/see-what-categories-new-users-other-trust-levels-post-in-most/80494 "2018-02-13T19:49:43Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![anon61128213](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@anon61128213](https://meta.discourse.org/u/anon61128213)\
**Post date:** [13 februari 2018 om 19:49 UTC](https://meta.discourse.org/t/see-what-categories-new-users-other-trust-levels-post-in-most/80494/1 "2018-02-13T19:49:43Z")

</div>

Is there a way to see which categories are most used by users that belong to a certain trust level?  
I’d especially be interested in what categories new users are most likely to start a topic in, but also where they post in general as well as how other trust levels behave.  
Posts and topics of e.g. regular users from before they ranked up should either not be counted at all or count for prior trust levels.

My theory is that new users are most likely to post (and especially start topics) in categories where you get help with something, whereas regular users will use categories for deep discussions more often.

---

<div class="post-metadata">

**Author:** ![bts](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/bts/32/184556_2.png) [@bts](https://meta.discourse.org/u/bts)\
**Post date:** [13 februari 2018 om 21:00 UTC](https://meta.discourse.org/t/see-what-categories-new-users-other-trust-levels-post-in-most/80494/2 "2018-02-13T21:00:14Z")

</div>

Sounds like a job for the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin! Very useful, you can learn more about it here:

> [@Discourse Data Explorer](https://meta.discourse.org/t/data-explorer-plugin/32566):
>
> discourse2Summary Discourse [Data Explorer](https://meta.discourse.org/t/32566?silent=true) allows you to make SQL queries against your live database, allowing for up-to-the-minute stats reporting.open_bookInstall Guide This plugin is bundled with Discourse core. There is no need to install the plugin separately.information_source If you’re looking for examples or support for any custom queries, you can find lots of topics in our #Data & reporting category under the #sql-query tag. If there’s not one to suit your pa…

Here’s a stab at a SQL query that does something like this — it takes `trust_level` as a parameter, and then returns a sorted list of the # of posts created in each category by users w/ that trust level (disclaimer, I’m a SQL novice, modify as needed!):

```plaintext
-- [params]
-- int :trust_level = 1

WITH topic_categories AS
    (SELECT
        t.id topic_id,
        t.user_id,
        t.created_at,
        t.score,
        c.id category_id,
        c.name category_name
    FROM
        topics t
    LEFT JOIN categories c
    ON t.category_id = c.id
    )

SELECT 
    count(tc.*) count,
    tc.category_id
FROM
    users u
LEFT JOIN topic_categories tc
ON u.id = tc.user_id
WHERE 
    tc.created_at IS NOT NULL
    AND tc.category_id IS NOT NULL
    AND u.trust_level = :trust_level
GROUP BY 
    tc.category_id
ORDER BY count DESC

```

---

<div class="post-metadata">

**Author:** ![gabrieltonon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/gabrieltonon/32/88155_2.png) [@gabrieltonon](https://meta.discourse.org/u/gabrieltonon)\
**Post date:** [13 februari 2018 om 22:03 UTC](https://meta.discourse.org/t/see-what-categories-new-users-other-trust-levels-post-in-most/80494/3 "2018-02-13T22:03:42Z")

</div>

That’s something I’d also be very interested in.

Recently I asked a similar question and it seems like out-of-the-box metrics for user interactions are [coming soon to the admin dashboard 2.0](https://meta.discourse.org/t/what-would-you-like-to-see-on-your-discourse-admin-dashboard/67134) 🤣

Not sure if this specific feature would also be included; perhaps @HAWK might also be able to confirm that.

> [@What would you like to see on your Discourse 2.0 Admin Dashboard?](https://meta.discourse.org/t/what-would-you-like-to-see-on-your-discourse-admin-dashboard/67134/70):
>
> I recently created a thread on this actually rofl I’d love to have more out-of-the-box metrics about our users (# interactions, replies, messages, reply times, etc). These are super helpful in customer-centric organizations.

---

<div class="post-metadata">

**Author:** ![HAWK](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/hawk/32/86627_2.png) [@HAWK](https://meta.discourse.org/u/HAWK)\
**Post date:** [13 februari 2018 om 22:39 UTC](https://meta.discourse.org/t/see-what-categories-new-users-other-trust-levels-post-in-most/80494/4 "2018-02-13T22:39:57Z")

</div>

The dashboard won’t specifically include this data. My challenge is to simplify the dashboard but make it useful for _all_ community types. For instance, CoP CMs need very different data to support community CMs. TL data is of very little (if any) relevance to the latter.

What we are planning as a compromise is to surface approved [data explorer](https://meta.discourse.org/t/32566?silent=true) queries via the dashboard so that mods have access.

Here is an early mockup:

 ![image](https://global.discourse-cdn.com/meta/original/3X/e/b/eb8d6810504ae0eba87889a5bff5dfda8e315442.jpg)