健全で包括的なコミュニティを維持するには、フラグ付き投稿のレビュー、モデレーターのパフォーマンス分析、ユーザーコンテンツの管理などを含む効果的なモデレーションが必要です。
このガイドには、モデレーション関連のアクティビティを分析するのに役立つ Discourse 用の多様な SQL レポートが含まれています。
このトピックでは、以下の詳細な Data Explorer クエリを見つけることができます。
- フラグ解決統計。
- レビュー対象の解決率。
- モデレーター固有のパフォーマンス指標。
- ユーザーのフラグ付けアクティビティに関する洞察。
- フラグ付きユーザー、投稿、トピックのアクションに関する包括的なデータ
種類別の投稿フラグ解決率
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（共通テーブル式）の説明
period_actions: 指定された日付範囲内のフラグ付き投稿をフィルタリングし、各フラグの解決までの時間を計算します。flag_types: フラグ種類の ID を人間が読みやすい名前にマッピングします（例：オフトピック、不適切、スパム）。flag_resolutions: フラグを種類と解決状況（同意、不同意、保留、削除）でグループ化し、各解決状況の発生回数をカウントします。flag_totals: 各フラグ種類のフラグ総数を計算します。resolution_percentages: 解決数とフラグ総数を組み合わせて、各フラグ種類の各解決タイプの割合を計算します。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 クエリの説明
このクエリは、指定された日付範囲内のレビュー対象アイテム（例：フラグ付き投稿）の解決ステータスを分析します。各フラグ種類ごとに、各解決ステータスの割合とカウントを計算します。
-- [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（共通テーブル式）の説明
flag_data: レビュー対象をフラグ種類と解決ステータスでグループ化し、各組み合わせの発生回数をカウントします。flag_totals: 各フラグ種類のフラグ総数を計算します。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 クエリの説明
このクエリは、どのモデレーターがフラグ付き投稿を解決し、処理したフラグの種類、適用した解決内容を示すことで、モデレーターのアクティビティに関する洞察を提供します。各解決タイプの割合とカウントを計算します。
-- [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（共通テーブル式）の説明
period_actions: 指定された日付範囲内のフラグ付き投稿をフィルタリングし、各フラグの解決までの時間を計算します。flag_types: フラグ種類の ID を人間が読みやすい名前にマッピングします。flag_resolutions: フラグをユーザー、フラグ種類、解決状況でグループ化し、各組み合わせの発生回数をカウントします。flag_totals: 各ユーザーおよびフラグ種類のフラグ総数を計算します。resolution_percentages: 解決数と総数を組み合わせて、各解決タイプの割合を計算します。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 クエリの説明
このクエリは、指定された日付範囲内で投稿をフラグ付けしたユーザーを特定し、各ユーザーが提出したフラグの総数を計算します。
-- [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 テーブルに格納されているユーザーノートを取得します。ユーザー ID、作成日、ノート内容、作成者の ID などの詳細を抽出します。
-- [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 | このユーザーは他の 2 つのユーザーアカウントと関連しています。 | 3 |
モデレーターの KPI - フラグ数と平均フラグ解決時間
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（共通テーブル式）の説明
period_actions: 指定された日付範囲内のフラグ付き投稿をフィルタリングし、各フラグの解決までの時間を計算します。moderator_actions: モデレーターが解決したフラグを特定し、各フラグの解決までの時間を計算します。moderator_stats: フラグをモデレーターごとにグループ化し、処理されたフラグの総数と平均解決時間を計算します。
結果の説明
最終結果は、処理されたフラグ数と平均解決時間ごとのモデレーターのランキングリストです。
結果の例
| モデレーターユーザー名 | 処理されたフラグ数 | 平均解決時間（分） |
|---|---|---|
| mod1 | 50 | 15.00 |
| mod2 | 30 | 20.00 |
すべてのフラグデータ
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（共通テーブル式）の説明
flag_data: フラグ ID、フラグ対象、フラグ種類、フラグ理由、フラグソース、レビュー詳細など、フラグ付き投稿に関する詳細情報を取得します。review_decisions: レビューステータスコードを人間が読みやすい決定名にマッピングします（例：保留中、同意、不同意、無視）。flag_types: 投稿アクションタイプの ID を人間が読みやすいフラグ種類名にマッピングします（例：オフトピック、不適切、スパム）。
結果の説明
- フラグ ID: フラグの一意の識別子。
- フラグ対象: フラグ付き投稿の ID。
- フラグ付けしたユーザー名: 投稿をフラグ付けしたユーザーのユーザー名。
- フラグ付け日: フラグが作成された日付。
- フラグの種類: フラグの数値タイプ。
- フラグ種類名: フラグ種類の人間が読みやすい名前（例：オフトピック、スパム）。
- モデレーターによるレビュー対象: フラグがユーザーによって、それともシステムによって提起されたかを示します。
- フラグ理由: フラグの理由。
- フラグ対象テキスト: フラグ付き投稿の内容。
- 関連メッセージ ID: 関連するメッセージの ID（該当する場合）。
- 関連メッセージテキスト: 関連メッセージの内容。明確化のために URL が削除されています。
- レビューしたユーザー名: フラグをレビューしたモデレーターのユーザー名。
- レビュー決定: レビュアーが行った決定（例：同意、不同意、無視、削除）。
- 実行されたアクション: フラグの結果として実行されたアクション。ユーザーのサイレンシングまたは停止、投稿の削除または非表示、またはアクションなし。
- レビュー時間（分）: フラグのレビューに要した時間。フラグの作成時間とレビュー時間の差を分で計算。
結果の例（匿名化）
結果の例（匿名化）
| フラグ ID | フラグ対象 | フラグ付けしたユーザー名 | フラグ付け日 | フラグの種類 | フラグ種類名 | モデレーターによるレビュー対象 | フラグ理由 | フラグ対象テキスト | 関連メッセージ ID | 関連メッセージテキスト | レビューしたユーザー名 | レビュー決定 | 実行されたアクション | レビュー時間（分） |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 12345 | 67890 | user123 | 2025-04-01 12:00 | 8 | Spam | true | Spam content | “Buy now at spam.com” | 98765 | “Check this out!” | mod456 | Agreed | Post deleted | 15.25 |
| 12346 | 67891 | user124 | 2025-04-02 14:30 | 4 | Inappropriate | false | Offensive | “This is inappropriate!” | NULL | NULL | mod457 | Disagreed | No action taken | 30.50 |
提出されたフラグレポート
このレポートは、指定された日付範囲内で提出されたフラグの概要を提供します。フラグを種類（例：スパム、不適切）で分類し、ユーザー報告のフラグとシステム生成のフラグを区別します。レポートには、各タイプのフラグ総数が含まれており、プラットフォームで最もよくフラグ付けされる問題を特定するのに役立ちます。
-- [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（共通テーブル式）の説明
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）。
flag_types:
このCTEは、数値の投稿アクションタイプIDを人間が読みやすいフラグタイプ名にマッピングします：
3: 無関係（Off-topic）4: 不適切（Inappropriate）6: ユーザーに通知（Notify user）7: モデレーターに通知（Notify moderators）8: スパム（Spam）10: 違法（Illegal）NULL: その他（Something else）
結果の説明
最終クエリはフラグタイプごとにフラグデータを集約し、以下のメトリクスを提供します：
- Type（タイプ）: フラグタイプの人間が読みやすい名前（例：無関係、スパム）。
- Reported（報告数）: ユーザーによって提出されたフラグの件数（システム生成フラグを除く）。
- Automated（自動検知数）: システムやボットによって生成されたフラグの件数（例：
spam_scanner_bot、system）。 - Total（合計）: 各タイプごとのフラグの総数。
結果はフラグの総数の降順でソートされます。
結果の例
| Type | Reported | Automated | Total |
|---|---|---|---|
| Spam | 120 | 80 | 200 |
| Inappropriate | 90 | 10 | 100 |
| Off-topic | 60 | 5 | 65 |
| Notify_moderators | 30 | 0 | 30 |
| Illegal | 10 | 2 | 12 |
| Something else | 5 | 0 | 5 |
BANおよびサスペンション（一時停止）:
このレポートは、指定された日付範囲内でサスペンションまたはサイレンス（発言禁止）されたユーザーを一覧表示します。サスペンションまたはサイレンスの日付、アクションの期間、およびユーザーのアカウント作成日と最終アクティビティ日などの詳細が含まれます。このレポートは、モデレーションアクションの監視や、BANやサイレンスにつながるユーザー行動のパターンを特定するのに役立ちます。
-- [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: サスペンションまたはサイレンスされたユーザーをフィルタリングする終了日。
結果の説明
このクエリは、指定された日付範囲内でサスペンションまたはサイレンスされたユーザーの一覧を取得します。主要なカラムには以下が含まれます：
- User ID（ユーザーID）: ユーザーの一意の識別子。
- Username（ユーザー名）: ユーザーのユーザー名。
- Name（名前）: ユーザーのフルネーム（利用可能な場合）。
- Suspended At（サスペンション日時）: ユーザーがサスペンションされた日付。
- Suspended Till（サスペンション終了日）: ユーザーがサスペンションされている期間の終了日。
- Silenced Till（サイレンス終了日）: ユーザーがサイレンスされている期間の終了日。
- Account Created At（アカウント作成日）: ユーザーのアカウントが作成された日付。
- Last Seen At（最終アクティビティ日時）: ユーザーがプラットフォーム上で最後にアクティブだった時間。
- Flag Level（フラグレベル）: ユーザーの現在のフラグレベル。
- Admin（管理者）: ユーザーが管理者かどうか（true/false）。
- Moderator（モデレーター）: ユーザーがモデレーターかどうか（true/false）。
結果はサスペンション日（suspended_at）とサイレンス日（silenced_till）の降順でソートされ、NULL値は最後に配置されます。
結果の例
| User ID | Username | Name | Suspended At | Suspended Till | Silenced Till | Account Created At | Last Seen At | Flag Level | Admin | Moderator |
|---|---|---|---|---|---|---|---|---|---|---|
| 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 |
合意されたフラグアクション
このレポートは、モデレーターによって合意され、アクションが実行されたフラグに焦点を当てています。フラグをタイプ別に分類し、フラグの総数、アクション実行までの中央値時間、および結果（例：ユーザーのサイレンス、投稿の削除）などのメトリクスを提供します。
-- [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 -- Only include flags that were agreed with and action was taken
),
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 -- Include mapped flag types
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Include specific flag types for NULL results
)
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 -- Include mapped flag types
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Include specific flag types for NULL results
GROUP BY
COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
Total DESC
使用されたパラメータ
:start_date: 合意されたフラグをフィルタリングする開始日。:end_date: 合意されたフラグをフィルタリングする終了日。
CTE（共通テーブル式）の説明
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）。 - フラグ付き投稿に関する追加情報、例えば投稿が削除または非表示にされたかどうか、または著者がサイレンスまたはサスペンションされたかどうか。
flag_types:
このCTEは、数値の投稿アクションタイプIDを人間が読みやすいフラグタイプ名にマッピングします：
3: 無関係（Off-topic）4: 不適切（Inappropriate）6: ユーザーに通知（Notify user）7: モデレーターに通知（Notify moderators）8: スパム（Spam）10: 違法（Illegal）
median_time_to_act:
このCTEは、各フラグタイプに対してアクションを実行するまでの中央値時間（分）を計算します。時間は、フラグ作成時間（flagged_date）とレビュー時間（reviewed_at）の差として計算されます。
結果の説明
最終クエリは、合意されたフラグデータをフラグタイプごとに集約し、以下のメトリクスを提供します：
- Type（タイプ）: フラグタイプの人間が読みやすい名前（例：無関係、スパム）。
- Reported（報告数）: ユーザーによって提出されたフラグの件数（システム生成フラグを除く）。
- Automated（自動検知数）: システムやボットによって生成されたフラグの件数（例：
spam_scanner_bot、system）。 - Total（合計）: 各タイプごとのフラグの総数。
- Median Time to Act (Minutes)（アクション実行までの中央値時間（分））: このタイプのフラグに対してアクションを実行するまでの中央値時間（分）。
- User Silenced（ユーザーサイレンス数）: ユーザーがサイレンスされたフラグの件数。
- User Deleted（ユーザー削除数）: ユーザーがサスペンションされたフラグの件数。
- Post Deleted（投稿削除数）: 投稿が削除されたフラグの件数。
- Post Hidden（投稿非表示数）: 投稿が非表示にされたフラグの件数。
結果はフラグの総数の降順でソートされます。
結果の例
| Type | Reported | Automated | Total | Median Time to Act (Minutes) | User Silenced | User Deleted | Post Deleted | Post Hidden |
|---|---|---|---|---|---|---|---|---|
| Spam | 100 | 50 | 150 | 30 | 20 | 10 | 50 | 30 |
| Inappropriate | 80 | 5 | 85 | 45 | 15 | 5 | 30 | 20 |
| Off-topic | 40 | 2 | 42 | 25 | 5 | 0 | 10 | 15 |
| Notify_moderators | 20 | 0 | 20 | 60 | 0 | 0 | 5 | 10 |
| Illegal | 5 | 1 | 6 | 120 | 1 | 1 | 3 | 2 |
実行されたモデレーションアクション
このレポートは、指定された日付範囲内で実行されたモデレーションアクションの概要を提供します。ユーザーまたは自動化によってフラグが立てられたコンテンツ、削除または非表示にされた投稿、発行された警告、削除またはサスペンションされたアカウント、および長期間サイレンスされたユーザーなど、さまざまなタイプの合意アクションを集約します。各カテゴリにはケースの総数が表示され、モデレーション活動の概要を高いレベルで提供します。
-- [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 -- Only include flags that were agreed with and action was taken
),
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 -- Include mapped flag types
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Include specific flag types for NULL results
),
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: モデレーションアクションをフィルタリングする終了日。
結果の説明
クエリはモデレーションアクションをカテゴリに集約し、各カテゴリのケースの総数を提供します。カテゴリには以下が含まれます：
- ユーザーによってフラグが立てられたコンテンツ: 一般ユーザーによってフラグが立てられた投稿の数（システム生成フラグを除く）。
- 自動化によってフラグが立てられたコンテンツ: 自動化システムやボットによってフラグが立てられた投稿の数（例：
spam_scanner_bot、system）。 - 規約違反により削除された投稿: コミュニティガイドラインまたは利用規約の違反により削除された投稿の数。
- 非表示にされた投稿: さまざまな理由で非表示（ただし削除はされていない）投稿の数。
- 発行された警告: 不適切な行動またはコンテンツに対してユーザーに発行された警告の数。
- 削除されたアカウント: スパマーとしてフラグが立てられたり、レビューキューで拒否されたりするなど、違反により削除されたユーザーアカウントの数。
- サスペンションされたアカウント: 違反により特定の期間サスペンションされたユーザーアカウントの数。
- 10年以上サイレンスされたユーザー: 無期限または長期（10年以上）にわたってサイレンスされたユーザーの数。
結果の例
| Category | Number of Cases |
|---|---|
| Content flagged by users | 150 |
| Content flagged by automation | 100 |
| Posts deleted for violating terms | 50 |
| Posts hidden | 30 |
| Warnings Issued | 20 |
| Accounts deleted | 10 |
| Accounts suspended | 15 |
| Users silenced for 10+ years | 5 |
実行された個別のモデレーションアクション
このレポートは、指定された日付範囲内で実行された合意された個別のモデレーションアクションの詳細なログを提供します。アクションを実行したユーザー、対象ユーザー、アクションの日付、アクションのカテゴリ（例：フラグ付きコンテンツ、削除された投稿、発行された警告）、およびアクションの文脈または理由に関する情報が含まれます。このレポートは、特定のモデレーション決定の監査や、各アクションの背景にある文脈を理解するのに役立ちます。
-- [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 -- Only include flags that were agreed with and action was taken
),
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: 個別のモデレーションアクションをフィルタリングする終了日。
結果の説明
クエリは個別のモデレーションアクションの詳細なログを提供し、以下が含まれます：
- Acting User（アクション実行者）: アクションを実行したモデレーター、システム、またはユーザーのユーザー名。
- Target User（対象ユーザー）: アクションの対象となったユーザーのユーザー名またはID（例：フラグ付き投稿の著者、または警告の受信者）。
- Action Date（アクション日時）: アクションが発生した日付と時刻。
- Category（カテゴリ）: モデレーションアクションの種類、例えば：
- ユーザーによってフラグが立てられたコンテンツ
- 自動化によってフラグが立てられたコンテンツ
- 規約違反により削除された投稿
- 非表示にされた投稿
- 発行された警告
- 削除されたアカウント
- サスペンションされたアカウント
- 10年以上サイレンスされたユーザー
- Context（文脈）: アクションに関連する追加情報またはコンテンツ、例えばフラグ付き投稿のテキストやサスペンションの理由。
結果の例
| Acting User | Target User | Action Date | Category | Context |
|---|---|---|---|---|
| user123 | user456 | 2024-02-01 10:00 | Content flagged by users | “この投稿にはスパムコンテンツが含まれています。” |
| spam_scanner | user789 | 2024-02-02 12:00 | Content flagged by automation | “システムによりスパムと検出されました。” |
| mod001 | user456 | 2024-02-03 14:00 | Posts deleted for violating terms | “例の投稿コンテンツ |
| mod002 | user123 | 2024-02-04 16:00 | Posts hidden | “投稿は不適切と判断されました。” |
| admin001 | user789 | 2024-02-05 18:00 | Warnings Issued | NULL |
| admin002 | user456 | 2024-02-06 20:00 | Accounts deleted | “アカウントはレビューキューを介して削除されました。” |
| mod003 | user123 | 2024-02-07 22:00 | Accounts suspended | “ユーザーは繰り返しのスパムによりサスペンションされました。” |
| admin003 | user789 | 2024-02-08 08:00 | Users silenced for 10+ years | “ユーザーは重大な違反によりサイレンスされました。” |