# 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:** 1
**Showing post:** 3

<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: [October 22, 2024, 7:10pm UTC](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/3 "2024-10-22T19:10:42Z")

</div>

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 |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244)._