Dashboard Report - Users Per Trust Level

Data & reporting
, ,
1

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.

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
1 Like