# Dashboard Report - Moderator Activity
**URL:** https://meta.discourse.org/t/dashboard-report-moderator-activity/291244
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [January 11, 2024, 1:07am UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244 "2024-01-11T01:07:56Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [January 11, 2024, 1:07am UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/1 "2024-01-11T01:07:56Z")
This is an SQL version of the Dashboard Report for Moderator Activity.
This report provides a comprehensive overview of the activities performed by moderators within a specified time frame on a Discourse site. The report combines multiple aspects of moderator activity: time spent on the platform, number of flags reviewed, posts created, personal messages (PMs) created, topics created, and post revisions made.
This dashboard report is a valuable tool for administrators looking to measure the effectiveness and engagement of their moderation team, providing a detailed look at their activity and contributions. The insights provided by the report can inform decisions on moderator training, recognition, and recruitment, and ensuring the moderation team is well-balanced and effective in maintaining community standards.
```sql
--[params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false
WITH mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
AND u.id > 0
),
time_read AS (
SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv ON m.user_id = uv.user_id
WHERE uv.visited_at >= :start_date
AND uv.visited_at <= :end_date
GROUP BY uv.user_id
),
flag_count AS (
WITH period_actions AS (
SELECT agreed_by_id,
disagreed_by_id
FROM post_actions
WHERE post_action_type_id IN (3,4,8,6,7)
AND created_at >= :start_date
AND created_at <= :end_date
),
agreed_flags AS (
SELECT pa.agreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.agreed_by_id = m.user_id
GROUP BY agreed_by_id
),
disagreed_flags AS (
SELECT pa.disagreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.disagreed_by_id = m.user_id
GROUP BY disagreed_by_id
)
SELECT
COALESCE(af.user_id, df.user_id) AS user_id,
COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
FROM agreed_flags af
FULL OUTER JOIN disagreed_flags df
ON df.user_id = af.user_id
),
revision_count AS (
SELECT pr.user_id,
COUNT(*) AS revision_count
FROM mods m
JOIN post_revisions pr
ON pr.user_id = m.user_id
JOIN posts p
ON p.id = pr.post_id
WHERE pr.created_at >= :start_date
AND pr.created_at <= :end_date
AND p.user_id <> pr.user_id
GROUP BY pr.user_id
),
topic_count AS (
SELECT t.user_id,
COUNT(*) AS topic_count
FROM mods m
JOIN topics t ON t.user_id = m.user_id
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY t.user_id
),
post_count AS (
SELECT p.user_id,
COUNT(*) AS post_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
),
pm_count AS (
SELECT p.user_id,
COUNT(*) AS pm_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
)
SELECT
m.user_id,
m.username,
fc.flag_count as flags_reviewed,
ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
tc.topic_count as topics_created,
pmc.pm_count as PMs_created,
pc.post_count as posts_created,
rc.revision_count as revisions
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
ORDER BY m.username ASC
```
### Parameters
- `:start_date` and `:end_date` - These parameters define the date range for the report. Both date parameters accept the date format of `YYYY-MM-DD`.
- `:include_admins` - This parameter determines whether to include administrators in the report alongside moderators.
### SQL Query Explanation
The report is structured using common table expressions (CTEs) to segment the data processing into manageable and logical sections. Here’s what happens in each CTE:
1. **mods** : Identifies all users with moderator status or admin status (if included by the `:include_admins` parameter). It selects only relevant user columns for further queries.
2. **time\_read** : Calculates the total time (in seconds) each moderator has spent reading content on the platform between the provided start and end dates.
3. **flag\_count** : Counts the number of flags that moderators have agreed with or disagreed with during the specified period. It takes into account multiple flag types represented by their respective post action type IDs.
4. **revision\_count** : Counts the number of post revisions made by moderators on other users’ posts within the given timeframe.
5. **topic\_count** : Counts the number of regular topics created by moderators.
6. **post\_count** : Counts the number of posts created by moderators in regular topics.
7. **pm\_count** : Counts the number of private messages initiated by moderators.
After collecting data in the CTEs, the main query joins them based on the user ID and compiles the final report displaying each moderator’s username, the total time spent reading (converted to hours), the number of flags reviewed, topics created, personal messages created, posts created, and revisions made. The results are ordered alphabetically by the moderator’s username.
### Example Results
| user | username | flags\_reviewed | time\_reading\_hours | topics\_created | pms\_created | posts\_created | revisions |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 1 | moderator1 | NULL | 36.11 | NULL | 344 | 8 | 15 |
| 2 | moderator2 | 46 | 104.52 | 2 | 271 | 466 | 363 |
| 3 | moderator3 | NULL | 72.15 | NULL | 418 | 64 | 16 |
---
### Author:  [@T\_Disco](https://meta.discourse.org/u/T_Disco)
#### Post date: [October 22, 2024, 11:27am UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/2 "2024-10-22T11:27:17Z")
Hi @SaraDev,
This is really useful. Would it be possible to add '‘Posts approved’ and ‘Posts rejected’ columns to the Moderator Activity report?
Thanks,
T\_Disco
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [October 22, 2024, 7:10pm UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/3 "2024-10-22T19:10:42Z")
Hi @T_Disco,
> [@T\_Disco](#):
>
> Would it be possible to add '‘Posts approved’ and ‘Posts rejected’ columns to the Moderator Activity report?
Yes, to add `Posts approved` and `Posts rejected` results to the Moderator Activity report, we can utilize the `reviewables` and `reviewable_scores` tables. Specifically, the `status` field in `reviewable_scores` can be used to determine whether a post was approved (status = 1) or rejected (status = 2).
Here’s what this would look like added to the report:
**Moderator Activity with Posts Approved and Posts Rejected**
```sql
-- [params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false
WITH mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
AND u.id > 0
),
time_read AS (
SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv ON m.user_id = uv.user_id
WHERE uv.visited_at >= :start_date
AND uv.visited_at <= :end_date
GROUP BY uv.user_id
),
flag_count AS (
WITH period_actions AS (
SELECT agreed_by_id,
disagreed_by_id
FROM post_actions
WHERE post_action_type_id IN (3,4,8,6,7)
AND created_at >= :start_date
AND created_at <= :end_date
),
agreed_flags AS (
SELECT pa.agreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.agreed_by_id = m.user_id
GROUP BY agreed_by_id
),
disagreed_flags AS (
SELECT pa.disagreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.disagreed_by_id = m.user_id
GROUP BY disagreed_by_id
)
SELECT
COALESCE(af.user_id, df.user_id) AS user_id,
COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
FROM agreed_flags af
FULL OUTER JOIN disagreed_flags df
ON df.user_id = af.user_id
),
revision_count AS (
SELECT pr.user_id,
COUNT(*) AS revision_count
FROM mods m
JOIN post_revisions pr
ON pr.user_id = m.user_id
JOIN posts p
ON p.id = pr.post_id
WHERE pr.created_at >= :start_date
AND pr.created_at <= :end_date
AND p.user_id <> pr.user_id
GROUP BY pr.user_id
),
topic_count AS (
SELECT t.user_id,
COUNT(*) AS topic_count
FROM mods m
JOIN topics t ON t.user_id = m.user_id
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY t.user_id
),
post_count AS (
SELECT p.user_id,
COUNT(*) AS post_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
),
pm_count AS (
SELECT p.user_id,
COUNT(*) AS pm_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
),
reviewable_actions AS (
SELECT
rs.reviewed_by_id AS user_id,
SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END) AS posts_approved,
SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) AS posts_rejected
FROM mods m
JOIN reviewable_scores rs ON rs.reviewed_by_id = m.user_id
JOIN reviewables r ON r.id = rs.reviewable_id
WHERE rs.reviewed_at >= :start_date
AND rs.reviewed_at <= :end_date
GROUP BY rs.reviewed_by_id
)
SELECT
m.user_id,
m.username,
fc.flag_count as flags_reviewed,
ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
tc.topic_count as topics_created,
pmc.pm_count as PMs_created,
pc.post_count as posts_created,
rc.revision_count as revisions,
ra.posts_approved,
ra.posts_rejected
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
LEFT JOIN reviewable_actions ra ON ra.user_id = m.user_id
ORDER BY m.username ASC
```
Where the results for this report would look like:
| user | username | flags\_reviewed | time\_reading\_hours | topics\_created | pms\_created | posts\_created | revisions | posts\_approved | posts\_rejected |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 1 | moderator1 | NULL | 36.11 | NULL | 344 | 8 | 15 | 10 | 5 |
| 2 | moderator2 | 46 | 104.52 | 2 | 271 | 466 | 363 | 7 | 3 |
| 3 | moderator3 | NULL | 72.15 | NULL | 418 | 64 | 16 | NULL | NULL |
---
### Author:  [@T\_Disco](https://meta.discourse.org/u/T_Disco)
#### Post date: [October 22, 2024, 9:17pm UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/4 "2024-10-22T21:17:17Z")
That’s brilliant, thank you @SaraDev!
Will those columns be added to the Moderator Activity report on the /admin/dashboard/moderation tab at some point?
Thanks again 🙂
T\_Disco
---
### Author:  [@srinivas.chilukuri](https://meta.discourse.org/u/srinivas.chilukuri)
#### Post date: [October 30, 2024, 5:36pm UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/5 "2024-10-30T17:36:04Z")
@SaraDev
Is a query possible for number of topic merges carried out in a given time period
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [October 30, 2024, 8:46pm UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/6 "2024-10-30T20:46:19Z")
> [@T\_Disco](#):
>
> Will those columns be added to the Moderator Activity report on the /admin/dashboard/moderation tab at some point?
We don’t currently have any plans to add those columns to the dashboard version of the report, but I can bring this up with our team to see if we can make some improvements in the future. 🙂
> [@srinivas.chilukuri](#):
>
> Is a query possible for number of topic merges carried out in a given time period
Yes, to create a query that shows when posts were moved to other topics, you can filter the `posts` table for entries where the `action_code` indicates a move with a `split_topic` entry.
For example:
**Individual Post Move Actions**
```sql
SELECT
id AS post_id,
user_id,
topic_id,
post_number,
created_at::date,
updated_at::date,
action_code
FROM
posts
WHERE
action_code = 'split_topic'
ORDER BY
created_at DESC
```
Would show results like:
| post | user | topic | post\_number | created\_at | updated\_at | action\_code |
| --- | --- | --- | --- | --- | --- | --- |
| A post was merged into an existing topic:: [Merged Topic Title] | USERNAME | Original\_Topic\_Title | 3 | 2024-10-30 | 2024-10-30 | split\_topic |
| 2 posts were merged into an existing topic:: [Merged Topic Title] | USERNAME | Original\_Topic\_Title | 5 | 2024-10-30 | 2024-10-30 | split\_topic |
| A post was split to a new topic: [Split Topic Title] | USERNAME | Original\_Topic\_Title | 2 | 2024-10-30 | 2024-10-30 | split\_topic |
| … | … | … | … | … | … | … |
If you wanted to show a total count of post moves for each user on a site, and add a couple parameters to filter the move actions by date, you could also use a query like the following:
**Number of Post Move Actions per User**
```sql
-- [params]
-- date :start_date
-- date :end_date
SELECT
user_id,
COUNT(*) AS move_count
FROM
posts
WHERE
action_code = 'split_topic'
AND created_at BETWEEN :start_date AND :end_date
GROUP BY
user_id
ORDER BY
move_count DESC
```
Where example results would look like:
| user | move\_count |
| --- | --- |
| Username\_1 | 5 |
| Username\_2 | 2 |
| … | … |
Note that with both of these queries, moving any number of posts from one topic to a different topic is only counted as **one** action, regardless of the number of posts that were moved. Moving the content on an entire topic to a different topic is also only counted as **one** action.
The number of posts that were moved during each action can be seen in the _Individual Post Move Actions_ query under the `post` column with the text `X posts were merged into an existing topic...`, however, this information is not present in the second query.
In the _Individual Post Move Actions_ query, you may also see posts with the text: `A post was split to a new topic ...`, which indicate that the post was split into a new topic instead of being moved into an existing topic, as Discourse considers both of these actions `split_topic` actions, since posts are being moved from one topic to a different topic.
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [February 27, 2025, 8:31pm UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/7 "2025-02-27T20:31:26Z")
5 posts were split to a new topic: [User Page Metrics](https://meta.discourse.org/t/user-page-metrics/354906)