# Dashboard Report - Users Per Trust Level

**URL:** https://meta.discourse.org/t/dashboard-report-users-per-trust-level/288565
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [December 13, 2023, 9:08pm UTC](https://meta.discourse.org/t/dashboard-report-users-per-trust-level/288565 "2023-12-13T21:08:49Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [December 13, 2023, 9:08pm UTC](https://meta.discourse.org/t/dashboard-report-users-per-trust-level/288565/1 "2023-12-13T21:08:49Z")

</div>

This is an SQL version of the Dashboard Report for Users Per Trust Level.

This report provides an overview of the distribution of user trust levels within a Discourse community.

```sql
SELECT 
    CASE
        WHEN trust_level = 0 THEN 'newuser'
        WHEN trust_level = 1 THEN 'basic'
        WHEN trust_level = 2 THEN 'member'
        WHEN trust_level = 3 THEN 'regular'
        WHEN trust_level = 4 THEN 'leader'
        ELSE 'unknow'
    END,
    COUNT(users) 
FROM users
WHERE 
    id > 0
    AND NOT EXISTS(
                     SELECT 1
                     FROM anonymous_users a
                     WHERE a.user_id = users.id
                  )
GROUP BY trust_level
ORDER BY trust_level

```

### Breakdown of the Query

- **SELECT CASE** : This part of the query uses a `CASE` statement to assign a human-readable name to each trust level. It translates the numeric `trust_level` field into strings like ‘newuser’, ‘basic’, ‘member’, ‘regular’, and ‘leader’ for easier understanding.
- **COUNT(users)**: This function counts the number of users within each trust level.
- **FROM users** : The query is pulling data from the `users` table, which contains all the user accounts in the community.
- **WHERE id \> 0** : This condition ensures that only real users are considered, excluding any system accounts that might have an ID of 0 or less.
- **AND NOT EXISTS** : This subquery filters out any users that are marked as anonymous by checking the `anonymous_users` table. If a user has an entry in the `anonymous_users` table, they are not included in the count.
- **GROUP BY trust\_level** : This clause groups the results by trust level, ensuring that the count is done separately for each level.
- **ORDER BY trust\_level** : Finally, the results are ordered by the `trust_level` field, ensuring that the output is sorted from the lowest level (newuser) to the highest (leader).

### Example Results

| case | count |
| --- | --- |
| newuser | 1235 |
| basic | 234 |
| member | 345 |
| regular | 56 |
| leader | 23 |