Dashboard Report - Users Per Type

data & reporting
, ,
1

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 column rk 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).
  • Each SELECT statement includes a hardcoded rank (rk) for ordering purposes.
  • The UNION operator is used to combine the results of the four SELECT 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 the rk column.

Example Results

Type Count
Admin 14
Moderator 30
Suspended 3
Silenced 2
1 Like