これは、ユーザーが投稿に立てたフラグをフォーラムモデレーターが処理する際の効率と応答性を分析するための Data Explorer SQL クエリです。
このレポートは、サイトごとに、指定された期間における各モデレーターが処理したフラグの数と、フラグを解決するまでの平均時間を示します。
モデレーターがフラグにどれだけ迅速かつ効果的に対応するかを理解することは、管理者がフォーラムをすべてユーザーにとって安全で歓迎的な空間に保つために役立ちます。
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-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
SQL クエリの説明
パラメータ
クエリは、分析対象の期間を定義するために 2 つのパラメータを使用します。
:start_date: 分析したい期間の開始日 (YYYY-MM-DD形式)。:end_date: 分析したい期間の終了日 (YYYY-MM-DD形式)。
CTE の説明
- period_actions: 指定された期間内に立てられたフラグを選択し、特定のフラグの種類に焦点を当てます。フラグが作成されてからモデレーターによって同意、不同意、または延期されるまでの解決にかかった時間（分単位）を計算します。
- moderator_actions:
period_actionsから、モデレーターによって対応されたアクションのみを含むようにフィルタリングします。各アクションを担当したモデレーターを特定します。 - moderator_stats:
moderator_actionsからデータを集計し、各モデレーターが処理したフラグの総数と、処理したフラグの平均解決時間（分単位）を計算します。
結果
クエリの最終的な出力は、処理したフラグの数でソートされたモデレーターのリストを提供し、平均解決時間で二次ソート（昇順）されます。各モデレーターについて、レポートは以下を示します。
moderator_username: モデレーターのユーザー名。handled_flags: 指定された期間内にモデレーターが処理したフラグの総数。avg_resolution_time_minutes: モデレーターがフラグを解決するのにかかった平均時間（小数点以下 2 桁に丸められます）。
結果例
| moderator_username | handled_flags | avg_resolution_time_minutes |
|---|---|---|
| Moderator1 | 343 | 39.41 |
| Moderator2 | 70 | 30.51 |
| Moderator3 | 63 | 42.21 |