This is an SQL version of the Dashboard Report for Users Per Type.
This report provides a count of users by specific roles and statuses within a Discourse community. It categorizes users into four distinct groups: Admins, Moderators, Suspended, and Silenced users. The purpose of this report is to give community managers a quick overview of the distribution of user types and those with restricted access due to suspension or silencing.
SELECT "Type", "Count" FROM (
SELECT 'Admin' AS "Type", COUNT(id) AS "Count", 1 rk FROM users WHERE admin = true AND id > 0
UNION SELECT 'Moderator', COUNT(id), 2 FROM users WHERE moderator = true AND id > 0
UNION SELECT 'Suspended', COUNT(id), 3 FROM users WHERE suspended_till > current_date
UNION SELECT 'Silenced', COUNT(id), 4 FROM users WHERE silenced_till > current_date) AS data
ORDER BY rk
SQL Query Explanation
- The query creates a derived table
data
with three columns: “Type”, “Count”, and a ranking columnrk
to ensure the final results are ordered logically (Admins, Moderators, Suspended, Silenced). - For each user role or status (Admin, Moderator, Suspended, Silenced), a
SELECT
statement counts the number of users that match the criteria:- Admins are identified by
admin = true
. - Moderators are identified by
moderator = true
. - Suspended users are those with a
suspended_till
date that is in the future (suspended_till > current_date
). - Silenced users are those with a
silenced_till
date that is in the future (silenced_till > current_date
).
- Admins are identified by
- Each
SELECT
statement includes a hardcoded rank (rk
) for ordering purposes. - The
UNION
operator is used to combine the results of the fourSELECT
statements into a single result set. - The outer
SELECT
statement then retrieves the “Type” and “Count” from the derived table and orders the results by therk
column.
Example Results
Type | Count |
---|---|
Admin | 14 |
Moderator | 30 |
Suspended | 3 |
Silenced | 2 |