# 分析版主和举报活动报告

**URL:** <https://meta.discourse.org/t/analyzing-moderation-and-flagging-activity-reports/352174>\
**Category:** Data & reporting\
**Tags:** moderation, sql-query\
**Created:** [2025年二月13日 23:08 UTC](https://meta.discourse.org/t/analyzing-moderation-and-flagging-activity-reports/352174 "2025-02-13T23:08:27Z")\
**Posts on this page:** 1\
**Showing post:** 1

<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:** [2025年二月13日 23:08 UTC](https://meta.discourse.org/t/analyzing-moderation-and-flagging-activity-reports/352174/1 "2025-02-13T23:08:27Z")

</div>

维护一个健康且包容的社区需要有效的管理，这可能包括审查被标记的帖子、分析管理员表现以及管理用户内容。

本指南包含多种用于 Discourse 的 SQL 报告，旨在帮助分析与管理工作相关的活动。

在本主题中，您将找到详细的 [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 查询，用于：

- 标记解决统计。
- 待审项目解决百分比。
- 特定管理员的性能指标。
- 用户标记活动的洞察。
- 所有被标记的用户、帖子和主题操作的全面数据

## 按类型划分的帖子标记解决百分比

### SQL 查询解释

此查询计算被标记帖子的解决情况（同意、不同意、暂缓、删除）的百分比，并按标记类型分组。

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH period_actions AS (
    SELECT pa.id,
           pa.post_action_type_id,
           pa.created_at,
           pa.agreed_at,
           pa.disagreed_at,
           pa.deferred_at,
           pa.agreed_by_id,
           pa.disagreed_by_id,
           pa.deferred_by_id,
           pa.deleted_at,
           pa.post_id,
           pa.user_id,
           COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
           EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 解决时间（分钟）
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8)
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
flag_types AS (
    SELECT pat.id,
           CASE 
               WHEN pat.id = 3 THEN 'off_topic'
               WHEN pat.id = 4 THEN 'inappropriate'
               WHEN pat.id = 6 THEN 'notify_user'
               WHEN pat.id = 7 THEN 'notify_moderators'
               WHEN pat.id = 8 THEN 'spam'
           END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT
        pa.post_action_type_id,
        CASE 
            WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
            WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
            WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.post_action_type_id
),
resolution_percentages AS (
    SELECT
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT
        flag_type,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_count ELSE 0 END) AS deleted_count,
        MAX(total_flags) AS total_flags
    FROM resolution_percentages
    GROUP BY flag_type
)
SELECT
    flag_type,
    agreed_percentage,
    agreed_count,
    disagreed_percentage,
    disagreed_count,
    deferred_percentage,
    deferred_count,
    deleted_percentage,
    deleted_count,
    total_flags
FROM pivoted_data
ORDER BY flag_type

```

### 使用的参数

- `:start_date`: 用于筛选被标记帖子的起始日期。
- `:end_date`: 用于筛选被标记帖子的结束日期。

### CTE 解释

1. **`period_actions`** : 筛选指定日期范围内的被标记帖子，并计算每个标记的解决时间。
2. **`flag_types`** : 将标记类型 ID 映射为人类可读的名称（例如：离题、不当、垃圾信息）。
3. **`flag_resolutions`** : 按类型和解决情况（同意、不同意、暂缓、删除）对标记进行分组，并统计每种解决情况的发生次数。
4. **`flag_totals`** : 计算每种标记类型的标记总数。
5. **`resolution_percentages`** : 结合解决计数和标记总数，计算每种标记类型的每种解决情况的百分比。
6. **`pivoted_data`** : 将数据透视，以便在单独的列中显示每种解决情况的百分比和计数。

### 结果解释

最终结果是一个表格，显示：

- 标记类型（例如：离题、垃圾信息）。
- 每种解决情况（同意、不同意、暂缓、删除）的百分比和计数。
- 每种标记类型的标记总数。

### 示例结果

| 标记类型 | 同意 % | 同意计数 | 不同意 % | 不同意计数 | 暂缓 % | 暂缓计数 | 删除 % | 删除计数 | 总标记数 |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| off\_topic | 50.00 | 25 | 30.00 | 15 | 10.00 | 5 | 10.00 | 5 | 50 |
| spam | 70.00 | 35 | 20.00 | 10 | 5.00 | 2 | 5.00 | 3 | 50 |

* * *

## 待审项目解决百分比

### SQL 查询解释

此查询分析给定日期范围内待审项目（例如被标记的帖子）的解决状态。它计算每种标记类型的每种解决状态的百分比和计数。

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH flag_data AS (
    SELECT
        r.type AS flag_type,
        r.status AS resolution_status,
        COUNT(*) AS flag_count
    FROM
        reviewables r
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        r.type,
        r.status
),
flag_totals AS (
    SELECT
        flag_type,
        SUM(flag_count) AS total_flags
    FROM
        flag_data
    GROUP BY
        flag_type
),
flag_percentages AS (
    SELECT
        fd.flag_type,
        fd.resolution_status,
        fd.flag_count,
        ROUND((fd.flag_count::decimal / ft.total_flags) * 100, 2) AS percentage
    FROM
        flag_data fd
    JOIN
        flag_totals ft
    ON
        fd.flag_type = ft.flag_type
)
SELECT
    fp.flag_type,
    -- 百分比
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.percentage END), 0) AS pending_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.percentage END), 0) AS approved_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.percentage END), 0) AS rejected_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.percentage END), 0) AS ignored_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.percentage END), 0) AS deleted_percentage,
    -- 计数
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.flag_count END), 0) AS pending_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.flag_count END), 0) AS approved_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.flag_count END), 0) AS rejected_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.flag_count END), 0) AS ignored_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.flag_count END), 0) AS deleted_count
FROM
    flag_percentages fp
GROUP BY
    fp.flag_type
ORDER BY
    fp.flag_type

```

### 使用的参数

- `:start_date`: 用于筛选待审项目的起始日期。
- `:end_date`: 用于筛选待审项目的结束日期。

### CTE 解释

1. **`flag_data`** : 按标记类型和解决状态对待审项目进行分组，统计每种组合的发生次数。
2. **`flag_totals`** : 计算每种标记类型的标记总数。
3. **`flag_percentages`** : 结合标记计数和总数，计算每种标记类型的每种解决状态的百分比。

### 结果解释

最终结果是一个表格，显示：

- 标记类型。
- 每种解决状态（待处理、批准、拒绝、忽略、删除）的百分比和计数。

### 示例结果

| 标记类型 | 待处理 % | 待处理计数 | 批准 % | 批准计数 | 拒绝 % | 拒绝计数 | 忽略 % | 忽略计数 | 删除 % | 删除计数 |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| off\_topic | 20.00 | 10 | 50.00 | 25 | 10.00 | 5 | 10.00 | 5 | 10.00 | 5 |
| spam | 10.00 | 5 | 70.00 | 35 | 10.00 | 5 | 5.00 | 2 | 5.00 | 3 |

* * *

## 管理员标记解决情况

### SQL 查询解释

此查询通过显示哪些管理员解决了被标记的帖子、他们处理的标记类型以及他们应用的解决情况，提供对管理员活动的洞察。它计算每种解决类型的百分比和计数。

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

WITH period_actions AS (
    SELECT 
        pa.id,
        pa.post_action_type_id,
        pa.created_at,
        pa.agreed_at,
        pa.disagreed_at,
        pa.deferred_at,
        pa.agreed_by_id,
        pa.disagreed_by_id,
        pa.deferred_by_id,
        pa.deleted_at,
        pa.post_id,
        pa.user_id,
        COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
        EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 解决时间（分钟）
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8)
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
flag_types AS (
    SELECT 
        pat.id,
        CASE 
            WHEN pat.id = 3 THEN 'off_topic'
            WHEN pat.id = 4 THEN 'inappropriate'
            WHEN pat.id = 6 THEN 'notify_user'
            WHEN pat.id = 7 THEN 'notify_moderators'
            WHEN pat.id = 8 THEN 'spam'
        END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        CASE 
            WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
            WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
            WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id
),
resolution_percentages AS (
    SELECT 
        fr.user_id,
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.user_id = fr.user_id AND ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT 
        rp.user_id,
        rp.flag_type,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_count ELSE 0 END) AS deleted_count,
        MAX(rp.total_flags) AS total_flags
    FROM resolution_percentages rp
    GROUP BY rp.user_id, rp.flag_type
)
SELECT 
    u.id AS user_id,
    u.username,
    p.flag_type,
    p.agreed_percentage,
    p.agreed_count,
    p.disagreed_percentage,
    p.disagreed_count,
    p.deferred_percentage,
    p.deferred_count,
    p.deleted_percentage,
    p.deleted_count,
    p.total_flags
FROM pivoted_data p
JOIN users u ON u.id = p.user_id
WHERE (:only_staff = false OR (u.admin = true OR u.moderator = true))
ORDER BY u.username, p.flag_type, p.total_flags

```

### 使用的参数

- `:start_date`: 用于筛选被标记帖子的起始日期。
- `:end_date`: 用于筛选被标记帖子的结束日期。
- `:only_staff`: 一个布尔参数，用于筛选结果以仅包含工作人员。

### CTE 解释

1. **`period_actions`** : 筛选指定日期范围内的被标记帖子，并计算每个标记的解决时间。
2. **`flag_types`** : 将标记类型 ID 映射为人类可读的名称。
3. **`flag_resolutions`** : 按用户、标记类型和解决情况对标记进行分组，统计每种组合的发生次数。
4. **`flag_totals`** : 计算每个用户和每种标记类型的标记总数。
5. **`resolution_percentages`** : 结合解决计数和总数，计算每种解决类型的百分比。
6. **`pivoted_data`** : 将数据透视，以便在单独的列中显示每种解决情况的百分比和计数。

### 结果解释

最终结果是一个表格，显示：

- 管理员用户名。
- 每种解决情况（同意、不同意、暂缓、删除）的百分比和计数。
- 每位管理员处理的标记总数。

### 示例结果

| 管理员 | 标记类型 | 同意 % | 同意计数 | 不同意 % | 不同意计数 | 暂缓 % | 暂缓计数 | 删除 % | 删除计数 | 总标记数 |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| mod1 | off\_topic | 60.00 | 30 | 20.00 | 10 | 10.00 | 5 | 10.00 | 5 | 50 |
| mod2 | spam | 70.00 | 35 | 20.00 | 10 | 5.00 | 2 | 5.00 | 3 | 50 |

* * *

## 谁在标记帖子

### SQL 查询解释

此查询识别在指定日期范围内标记帖子的用户，并计算每个用户提交的标记总数。

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

SELECT 
    u.id AS user_id,
    u.username,
    COUNT(pa.id) AS flag_count
FROM post_actions pa
JOIN users u ON u.id = pa.user_id
WHERE pa.post_action_type_id IN (3, 4, 6, 7, 8) -- 标记类型
  AND pa.created_at >= :start_date
  AND pa.created_at <= :end_date
  AND (:only_staff = false OR (u.admin = true OR u.moderator = true))
GROUP BY u.id, u.username
ORDER BY flag_count DESC, u.username
LIMIT 10

```

### 使用的参数

- `:start_date`: 用于筛选被标记帖子的起始日期。
- `:end_date`: 用于筛选被标记帖子的结束日期。
- `:only_staff`: 一个布尔参数，用于筛选结果以仅包含工作人员。

### 结果解释

最终结果是一个按标记计数排名的用户列表。

### 示例结果

| 用户 ID | 用户名 | 标记计数 |
| --- | --- | --- |
| 1 | user1 | 50 |
| 2 | user2 | 30 |
| 3 | user3 | 20 |

* * *

## 用户备注

### SQL 查询解释

此查询从 `plugin_store_rows` 表中检索 [用户备注](https://meta.discourse.org/t/discourse-user-notes/41026)。它提取详细信息，如用户 ID、创建日期、备注内容和创建者 ID。

```sql
-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-01-01

WITH user_notes AS (

    SELECT 
        REPLACE(key, 'notes:', '')::int AS user_id,
        notes.value->>'created_at' AS created_at,
        notes.value->>'raw' AS user_note,
        notes.value->>'created_by' AS created_by
    FROM plugin_store_rows,
    LATERAL json_array_elements(value::json) notes
    WHERE plugin_name = 'user_notes'
    ORDER BY 2 DESC 
)

SELECT 
    un.user_id,
    un.created_at::date,
    un.user_note,
    un.created_by AS created_by_user_id
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at::date BETWEEN :start_date AND :end_date
ORDER BY created_at DESC

```

### 使用的参数

- `:start_date`: 用于筛选用户备注的起始日期。
- `:end_date`: 用于筛选用户备注的结束日期。

### 结果解释

最终结果是一个包含相关元数据的详细用户备注列表。

### 示例结果

| 用户 ID | 创建日期 | 用户备注 | 创建者用户 ID |
| --- | --- | --- | --- |
| 1 | 2025-01-01 | 这个用户很乐于助人。 | 2 |
| 2 | 2025-02-01 | 这个用户与另外两个用户账户有关联。 | 3 |

* * *

## 管理员 KPI - 标记和平均标记解决时间

### SQL 查询解释

此查询通过计算每位管理员处理的标记数量和平均解决时间（分钟）来评估管理员表现。

```sql
-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-01-01

WITH period_actions AS (
    SELECT pa.id,
           pa.post_action_type_id,
           pa.created_at,
           pa.agreed_at,
           pa.disagreed_at,
           pa.deferred_at,
           pa.agreed_by_id,
           pa.disagreed_by_id,
           pa.deferred_by_id,
           pa.post_id,
           pa.user_id,
           COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
           EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 解决时间（分钟）
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- 标记类型
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
moderator_actions AS (
    SELECT pa.id,
           pa.post_id,
           pa.created_at,
           pa.responded_at,
           pa.time_to_resolution_minutes,
           COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) AS moderator_id
    FROM period_actions pa
    WHERE COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) IS NOT NULL
),
moderator_stats AS (
    SELECT
        m.moderator_id,
        u.username AS moderator_username,
        COUNT(m.id) AS handled_flags,
        AVG(m.time_to_resolution_minutes) AS avg_resolution_time_minutes
    FROM moderator_actions m
    JOIN users u ON u.id = m.moderator_id
    GROUP BY m.moderator_id, u.username
)
SELECT
    ms.moderator_username,
    ms.handled_flags,
    ROUND(ms.avg_resolution_time_minutes::numeric, 2) AS avg_resolution_time_minutes
FROM moderator_stats ms
ORDER BY ms.handled_flags DESC, ms.avg_resolution_time_minutes ASC

```

### 使用的参数

- `:start_date`: 用于筛选被标记帖子的起始日期。
- `:end_date`: 用于筛选被标记帖子的结束日期。

### CTE 解释

1. **`period_actions`** : 筛选指定日期范围内的被标记帖子，并计算每个标记的解决时间。
2. **`moderator_actions`** : 识别由管理员解决的标记，并计算每个标记的解决时间。
3. **`moderator_stats`** : 按管理员对标记进行分组，并计算处理的标记总数和平均解决时间。

### 结果解释

最终结果是一个按处理标记计数和平均解决时间排名的管理员列表。

### 示例结果

| 管理员用户名 | 处理标记数 | 平均解决时间（分钟） |
| --- | --- | --- |
| mod1 | 50 | 15.00 |
| mod2 | 30 | 20.00 |

* * *

## 所有标记数据

### SQL 查询解释

此查询提供指定日期范围内所有被标记的用户、帖子和主题数据的全面数据集。它结合多个表的数据，包括标记类型、被标记项目、标记原因、标记来源、解决决策和相关消息等详细信息。

```sql
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        r.potentially_illegal AS potentially_illegal, 
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        p.hidden_at AS post_hidden_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        --AND r.status = 1 -- 仅包含同意并采取行动的标记
),
review_decisions AS (
    SELECT
        0 AS status_code, 'pending' AS decision_name
    UNION ALL
    SELECT
        1 AS status_code, 'agreed' AS decision_name
    UNION ALL
    SELECT
        2 AS status_code, 'disagreed' AS decision_name
    UNION ALL
    SELECT
        3 AS status_code, 'ignored' AS decision_name
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'off_topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'illegal' AS flag_type_name
)
SELECT
    fd.flag_id,
    fd.post_id AS flagged_item,
    fd.flagged_by_username,
    fd.flagged_date,
    fd.flag_type,
    ft.flag_type_name AS flag_type_name,
    fd.flag_source AS reviewable_by_moderator,
    fd.flag_reason,
    fd.flagged_item_text,
    pa.related_post_id AS related_message_id_post_id,
    regexp_replace(rp.raw, '(https?://[^\s]+)', '', 'g') AS related_message_text, -- 仅移除 URL
    fd.reviewed_at,
    fd.reviewed_by_username AS reviewed_by,
    rd.decision_name AS review_decision,
    CASE 
        WHEN fd.user_silenced_till IS NOT NULL THEN 'User silenced'
        WHEN fd.user_suspended_till IS NOT NULL THEN 'User suspended'
        WHEN fd.post_deleted_at IS NOT NULL THEN 'Post deleted'
        WHEN fd.post_hidden_at IS NOT NULL THEN 'Post hidden'
        ELSE 'No action taken'
    END AS action_taken,
    CASE 
        WHEN fd.reviewed_at IS NOT NULL THEN ROUND(EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date)) / 60, 2)
        ELSE NULL
    END AS review_time_minutes -- 时间差（分钟）
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN posts rp 
    ON pa.related_post_id = rp.id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
LEFT JOIN review_decisions rd 
    ON fd.review_status = rd.status_code
ORDER BY
    fd.flagged_date DESC

```

### 使用的参数

- `:start_date`: 用于筛选被标记帖子的起始日期。
- `:end_date`: 用于筛选被标记帖子的结束日期。

### CTE 解释

1. **`flag_data`** : 检索有关被标记帖子的详细信息，包括标记 ID、被标记项目、标记类型、标记原因、标记来源和审查详情。
2. **`review_decisions`** : 将审查状态代码映射为人类可读的决策名称（例如：待处理、同意、不同意、忽略）。
3. **`flag_types`** : 将帖子操作类型 ID 映射为人类可读的标记类型名称（例如：离题、不当、垃圾信息）。

### 结果解释

- **标记 ID** : 标记的唯一标识符。
- **被标记项目** : 被标记帖子的 ID。
- **标记者用户名** : 标记该帖子的用户的用户名。
- **标记日期** : 创建标记的日期。
- **标记类型** : 标记的数字类型。
- **标记类型名称** : 标记类型的人类可读名称（例如：离题、垃圾信息）。
- **管理员可审查** : 指示标记是由用户还是系统提出的。
- **标记原因** : 提供标记的原因。
- **被标记项目文本** : 被标记帖子的内容。
- **相关消息 ID** : 任何相关消息的 ID（如果适用）。
- **相关消息文本** : 相关消息的内容，为清晰起见移除了 URL。
- **审查者用户名** : 审查标记的管理员的用户名。
- **审查决策** : 审查者做出的决策（例如：同意、不同意、忽略、删除）。
- **采取的行动** : 因标记而采取的行动，例如禁言或暂停用户、删除或隐藏帖子，或未采取行动。
- **审查时间（分钟）** : 审查标记所花费的时间，计算为标记创建时间和审查时间之间的差值，以分钟为单位。

### 示例结果（匿名化）

### 示例结果（匿名化）

| 标记 ID | 被标记项目 | 标记者用户名 | 标记日期 | 标记类型 | 标记类型名称 | 管理员可审查 | 标记原因 | 被标记项目文本 | 相关消息 ID | 相关消息文本 | 审查者用户名 | 审查决策 | 采取的行动 | 审查时间（分钟） |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 12345 | 67890 | user123 | 2025-04-01 12:00 | 8 | Spam | true | 垃圾内容 | “立即在 [spam.com](http://spam.com/) 购买” | 98765 | “看看这个！” | mod456 | Agreed | 帖子已删除 | 15.25 |
| 12346 | 67891 | user124 | 2025-04-02 14:30 | 4 | Inappropriate | false | 冒犯性 | “这不合适！” | NULL | NULL | mod457 | Disagreed | 未采取行动 | 30.50 |

## 提交的标记报告

此报告概述了指定日期范围内提交的标记。它按类型（例如：垃圾信息、不当）对标记进行分类，并区分用户报告的标记和系统生成的标记。该报告包括每种类型的标记总数，有助于识别平台上最常见的被标记问题。

```sql
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
    UNION ALL
    SELECT
        NULL AS post_action_type_id, 'Something else' AS flag_type_name
)
SELECT
    ft.flag_type_name AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
GROUP BY
    ft.flag_type_name
ORDER BY
    Total DESC

```

### 使用的参数

- `:start_date`: 用于筛选标记帖子的开始日期。
- `:end_date`: 用于筛选标记帖子的结束日期。

### CTE 说明

1. **`flag_data`** ：  
此 CTE 检索有关标记帖子的详细信息，包括：

- 标记的唯一 ID (`flag_id`)、被标记帖子的 ID (`post_id`) 以及其所属的主题 (`topic_id`)。
- 标记本身的信息，例如标记该帖子的用户 (`flagged_by_username`)、标记日期 (`flagged_date`)、标记类型 (`flag_type`) 以及标记原因 (`flag_reason`)。
- 审核过程的详细信息，包括审核标记的管理员 (`reviewed_by_username`)、审核决定 (`review_status`) 以及审核时间 (`reviewed_at`)。

1. **`flag_types`** ：  
此 CTE 将数字帖子操作类型 ID 映射为人类可读的标记类型名称：

- `3`: 离题
- `4`: 不当
- `6`: 通知用户
- `7`: 通知管理员
- `8`: 垃圾信息
- `10`: 非法
- `NULL`: 其他

### 结果说明

最终查询按标记类型聚合标记数据，并提供以下指标：

- **类型** ：标记类型的人类可读名称（例如，离题、垃圾信息）。
- **用户报告** ：由用户提交的标记数量（不包括系统生成的标记）。
- **自动** ：由系统或机器人生成的标记数量（例如，`spam_scanner_bot`、`system`）。
- **总计** ：每种类型的标记总数。

结果按标记总数降序排列。

### 示例结果

| 类型 | 用户报告 | 自动 | 总计 |
| --- | --- | --- | --- |
| 垃圾信息 | 120 | 80 | 200 |
| 不当 | 90 | 10 | 100 |
| 离题 | 60 | 5 | 65 |
| 通知管理员 | 30 | 0 | 30 |
| 非法 | 10 | 2 | 12 |
| 其他 | 5 | 0 | 5 |

## 封禁和暂停：

此报告列出了在指定日期范围内被暂停或禁言的用户。其中包括暂停或禁言日期、操作持续时间以及用户账户创建和最后活动日期等详细信息。此报告有助于监控管理操作，并识别导致封禁或禁言的用户行为模式。

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2026-01-01
SELECT 
    u.id AS user_id,
    u.username,
    u.name,
    u.suspended_at,
    u.suspended_till,
    u.silenced_till,
    u.created_at AS account_created_at,
    u.last_seen_at,
    u.flag_level,
    u.admin,
    u.moderator
FROM 
    users u
WHERE 
    (
        u.suspended_at BETWEEN :start_date AND :end_date
        OR u.silenced_till BETWEEN :start_date AND :end_date
    )
ORDER BY 
    u.suspended_at DESC NULLS LAST,
    u.silenced_till DESC NULLS LAST

```

### 使用的参数

- `:start_date`: 用于筛选被暂停或禁言用户的开始日期。
- `:end_date`: 用于筛选被暂停或禁言用户的结束日期。

### 结果说明

此查询检索在指定日期范围内被暂停或禁言的用户列表。关键列包括：

- **用户 ID** ：用户的唯一标识符。
- **用户名** ：用户的用户名。
- **姓名** ：用户的全名（如果可用）。
- **暂停时间** ：用户被暂停的日期。
- **暂停至** ：用户被暂停的截止日期。
- **禁言至** ：用户被禁言的截止日期。
- **账户创建时间** ：用户账户创建的日期。
- **最后在线时间** ：用户在平台上最后活跃的时间。
- **标记等级** ：用户当前的标记等级。
- **管理员** ：用户是否为管理员（真/假）。
- **版主** ：用户是否为版主（真/假）。

结果按暂停日期 (`suspended_at`) 和禁言日期 (`silenced_till`) 降序排列，空值排在最后。

#### 示例结果

| 用户 ID | 用户名 | 姓名 | 暂停时间 | 暂停至 | 禁言至 | 账户创建时间 | 最后在线时间 | 标记等级 | 管理员 | 版主 |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 101 | user123 | John Doe | 2025-03-15 10:00 | 2025-04-15 10:00 | NULL | 2020-01-01 12:00 | 2025-03-14 18:00 | 2 | false | false |
| 102 | user456 | Jane Smith | NULL | NULL | 2025-03-20 18:00 | 2021-06-10 15:00 | 2025-03-19 20:00 | 1 | false | false |
| 103 | mod789 | Moderator1 | 2025-02-01 08:00 | 2025-03-01 08:00 | NULL | 2019-05-05 10:00 | 2025-01-31 22:00 | 3 | false | true |
| 104 | admin001 | AdminUser | NULL | NULL | 2025-03-25 12:00 | 2018-12-25 09:00 | 2025-03-24 16:00 | 0 | true | false |

## 已同意的标记操作

此报告侧重于版主同意并采取了行动的标记。它按类型对标记进行分类，并提供诸如标记总数、处理标记的中位时间以及结果（例如，用户被禁言、帖子被删除）等指标。

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- 仅包括已同意并采取行动的标记
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
median_time_to_act AS (
    SELECT
        COALESCE(ft.flag_type_name, fd.flag_type) AS flag_type,
        ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date))) / 60) AS median_time_minutes
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        fd.reviewed_at IS NOT NULL
        AND (
            ft.flag_type_name IS NOT NULL -- 包括映射的标记类型
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- 包括 NULL 结果的特定标记类型
        )
    GROUP BY
        COALESCE(ft.flag_type_name, fd.flag_type)
)
SELECT
    COALESCE(ft.flag_type_name, fd.flag_type) AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total,
    COALESCE(mta.median_time_minutes, 0) AS "Median time to act (minutes)",
    COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "User silenced",
    COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "User deleted",
    COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post deleted",
    COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post hidden"

FROM
    flag_data fd
LEFT JOIN flag_types ft 
    ON fd.post_action_type_id = ft.post_action_type_id
LEFT JOIN median_time_to_act mta 
    ON COALESCE(ft.flag_type_name, fd.flag_type) = mta.flag_type
WHERE
    ft.flag_type_name IS NOT NULL -- 包括映射的标记类型
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- 包括 NULL 结果的特定标记类型
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

```

### 使用的参数

- `:start_date`: 用于筛选已同意标记的开始日期。
- `:end_date`: 用于筛选已同意标记的结束日期。

### CTE 说明

1. **`flag_data`** ：  
此 CTE 检索有关已同意并采取行动的标记的详细信息。它包括：

- 标记的唯一 ID (`flag_id`)、被标记帖子的 ID (`post_id`) 以及其所属的主题 (`topic_id`)。
- 标记本身的信息，例如标记该帖子的用户 (`flagged_by_username`)、标记日期 (`flagged_date`)、标记类型 (`flag_type`) 以及标记原因 (`flag_reason`)。
- 审核过程的详细信息，包括审核标记的管理员 (`reviewed_by_username`)、审核决定 (`review_status`) 以及审核时间 (`reviewed_at`)。
- 有关被标记帖子的其他信息，例如是否被删除、隐藏，或者作者是否被禁言或暂停。

1. **`flag_types`** ：  
此 CTE 将数字帖子操作类型 ID 映射为人类可读的标记类型名称：

- `3`: 离题
- `4`: 不当
- `6`: 通知用户
- `7`: 通知管理员
- `8`: 垃圾信息
- `10`: 非法

1. **`median_time_to_act`** ：  
此 CTE 计算处理每种标记类型所需的中位时间（以分钟为单位）。时间计算为标记创建时间 (`flagged_date`) 与审核时间 (`reviewed_at`) 之间的差值。

### 结果说明

最终查询按标记类型聚合已同意的标记数据，并提供以下指标：

- **类型** ：标记类型的人类可读名称（例如，离题、垃圾信息）。
- **用户报告** ：由用户提交的标记数量（不包括系统生成的标记）。
- **自动** ：由系统或机器人生成的标记数量（例如，`spam_scanner_bot`、`system`）。
- **总计** ：每种类型的标记总数。
- **处理中位时间（分钟）** ：处理此类标记所需的中位时间，以分钟为单位。
- **用户被禁言** ：导致用户被禁言的标记数量。
- **用户被删除** ：导致用户被暂停的标记数量。
- **帖子被删除** ：导致帖子被删除的标记数量。
- **帖子被隐藏** ：导致帖子被隐藏的标记数量。

结果按标记总数降序排列。

#### 示例结果

| 类型 | 用户报告 | 自动 | 总计 | 处理中位时间（分钟） | 用户被禁言 | 用户被删除 | 帖子被删除 | 帖子被隐藏 |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 垃圾信息 | 100 | 50 | 150 | 30 | 20 | 10 | 50 | 30 |
| 不当 | 80 | 5 | 85 | 45 | 15 | 5 | 30 | 20 |
| 离题 | 40 | 2 | 42 | 25 | 5 | 0 | 10 | 15 |
| 通知管理员 | 20 | 0 | 20 | 60 | 0 | 0 | 5 | 10 |
| 非法 | 5 | 1 | 6 | 120 | 1 | 1 | 3 | 2 |

## 采取的管理操作

此报告提供了指定日期范围内采取的管理操作的摘要。它聚合了各种类型的已同意操作，包括由用户或自动化系统标记的内容、被删除或隐藏的帖子、发出的警告、被删除或暂停的账户，以及被长期禁言的用户。每个类别都展示了案例总数，提供了管理活动的高层概览。

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- 仅包括已同意并采取行动的标记
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS user_flagged,
        COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS automation_flagged
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL -- 包括映射的标记类型
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- 包括 NULL 结果的特定标记类型
),
warnings_issued AS (
    SELECT
        COUNT(*) AS warnings_count
    FROM
        user_warnings
    WHERE
        created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        COUNT(CASE 
            WHEN uh.action = 1 
                AND (
                    LOWER(uh.context) LIKE '%deleted via review queue%' OR
                    LOWER(uh.context) LIKE '%to be a spammer%' OR
                    LOWER(uh.context) LIKE '%review%' OR
                    LOWER(uh.context) LIKE '%reviewable user rejected%'
                ) 
            THEN 1 
        END) AS accounts_deleted,
        COUNT(CASE WHEN uh.action = 10 THEN 1 END) AS accounts_suspended
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
),
posts_deleted_and_hidden AS (
    SELECT
        COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS posts_deleted,
        COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS posts_hidden
    FROM
        flag_data fd
),
silences_issued AS (
    SELECT
        COUNT(*) AS silences_count
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    'Content flagged by users' AS category,
    fc.user_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Content flagged by automation' AS category,
    fc.automation_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Posts deleted for violating terms' AS category,
    pdh.posts_deleted AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Posts hidden' AS category,
    pdh.posts_hidden AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Warnings Issued' AS category,
    wi.warnings_count AS "Number of Cases"
FROM warnings_issued wi

UNION ALL

SELECT
    'Accounts deleted' AS category,
    vs.accounts_deleted AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Accounts suspended' AS category,
    vs.accounts_suspended AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Users silenced for 10+ years' AS category,
    si.silences_count AS "Number of Cases"
FROM silences_issued si

```

### 使用的参数

- `:start_date`: 用于筛选管理操作的开始日期。
- `:end_date`: 用于筛选管理操作的结束日期。

### 结果说明

查询将管理操作聚合为类别，并提供每个类别的案例总数。类别包括：

1. **用户标记的内容** ：由普通用户标记的帖子数量（不包括系统生成的标记）。
2. **自动化标记的内容** ：由自动化系统或机器人标记的帖子数量（例如，`spam_scanner_bot`、`system`）。
3. **因违反条款而被删除的帖子** ：因违反社区准则或服务条款而被删除的帖子数量。
4. **被隐藏的帖子** ：因各种原因被隐藏（但未删除）的帖子数量。
5. **发出的警告** ：因不当行为或内容向用户发出的警告数量。
6. **被删除的账户** ：因违规（例如被标记为垃圾信息发送者或在审核队列中被拒绝）而被删除的用户账户数量。
7. **被暂停的账户** ：因违规而被暂停特定时间的用户账户数量。
8. **被禁言 10 年以上的用户** ：被无限期或长期（10 年以上）禁言的用户数量。

### 示例结果

| 类别 | 案例数 |
| --- | --- |
| 用户标记的内容 | 150 |
| 自动化标记的内容 | 100 |
| 因违反条款而被删除的帖子 | 50 |
| 被隐藏的帖子 | 30 |
| 发出的警告 | 20 |
| 被删除的账户 | 10 |
| 被暂停的账户 | 15 |
| 被禁言 10 年以上的用户 | 5 |

## 采取的个人管理操作

此报告提供了指定日期范围内采取的个人管理操作的详细日志。其中包括执行操作的用户、目标用户、操作日期、操作类别（例如，标记内容、删除帖子、发出警告）以及操作的背景或原因等信息。此报告有助于审计特定的管理决定，并了解每个操作背后的背景。

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- 仅包括已同意并采取行动的标记
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by users' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system')
    
    UNION ALL

    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by automation' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username IN ('spam_scanner_bot', 'system')
),
posts_deleted_and_hidden AS (
    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_deleted_at AS action_date,
        'Posts deleted for violating terms' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_deleted_at IS NOT NULL
    
    UNION ALL

    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_hidden_at AS action_date,
        'Posts hidden' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_hidden_at IS NOT NULL
),
warnings_issued AS (
    SELECT
        CAST(uw.created_by_id AS TEXT) AS acting_user,
        CAST(uw.user_id AS TEXT) AS target_user,
        uw.created_at AS action_date,
        'Warnings Issued' AS category,
        NULL AS context
    FROM
        user_warnings uw
    WHERE
        uw.created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts deleted' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 1
        AND (
            LOWER(uh.context) LIKE '%deleted via review queue%' OR
            LOWER(uh.context) LIKE '%to be a spammer%' OR
            LOWER(uh.context) LIKE '%review%' OR
            LOWER(uh.context) LIKE '%reviewable user rejected%'
        )
    
    UNION ALL

    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts suspended' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 10
),
silences_issued AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Users silenced for 10+ years' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM flagged_content

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM posts_deleted_and_hidden

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM warnings_issued

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM violations_and_suspensions

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM silences_issued

```

### 使用的参数

- `:start_date`: 用于筛选个人管理操作的开始日期。
- `:end_date`: 用于筛选个人管理操作的结束日期。

### 结果说明

查询提供了个人管理操作的详细日志，包括：

1. **执行用户** ：执行操作的管理员、系统或用户的用户名。
2. **目标用户** ：作为操作对象的用户（例如，被标记帖子的作者或收到警告的接收者）的用户名或 ID。
3. **操作日期** ：操作发生的日期和时间。
4. **类别** ：管理操作的类型，例如：

- 用户标记的内容
- 自动化标记的内容
- 因违反条款而被删除的帖子
- 被隐藏的帖子
- 发出的警告
- 被删除的账户
- 被暂停的账户
- 被禁言 10 年以上的用户

1. **背景** ：与操作相关的其他信息或内容，例如被标记帖子的文本或暂停的原因。

### 示例结果

| 执行用户 | 目标用户 | 操作日期 | 类别 | 背景 |
| --- | --- | --- | --- | --- |
| user123 | user456 | 2024-02-01 10:00 | 用户标记的内容 | “此帖子包含垃圾信息内容。” |
| spam\_scanner | user789 | 2024-02-02 12:00 | 自动化标记的内容 | “被系统检测为垃圾信息。” |
| mod001 | user456 | 2024-02-03 14:00 | 因违反条款而被删除的帖子 | “示例帖子内容 |
| mod002 | user123 | 2024-02-04 16:00 | 被隐藏的帖子 | “帖子被视为不当。” |
| admin001 | user789 | 2024-02-05 18:00 | 发出的警告 | NULL |
| admin002 | user456 | 2024-02-06 20:00 | 被删除的账户 | “账户通过审核队列被删除。” |
| mod003 | user123 | 2024-02-07 22:00 | 被暂停的账户 | “用户因重复发送垃圾信息被暂停。” |
| admin003 | user789 | 2024-02-08 08:00 | 被禁言 10 年以上的用户 | “用户因严重违规被禁言。” |

---

_[View the full topic](https://meta.discourse.org/t/analyzing-moderation-and-flagging-activity-reports/352174)._