ダッシュボードレポート - フラグステータス

データとレポート
, ,
1

これは、フラグステータスに関するダッシュボードレポートの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 -- time to resolution in 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
),
poster_data AS (
    SELECT pa.id,
           p.user_id AS poster_id,
           p.topic_id,
           p.post_number,
           u.username_lower AS poster_username,
           u.uploaded_avatar_id AS poster_avatar_id
    FROM period_actions pa
    JOIN posts p ON p.id = pa.post_id
    JOIN users u ON u.id = p.user_id
),
flagger_data AS (
    SELECT pa.id,
           u.id AS flagger_id,
           u.username_lower AS flagger_username,
           u.uploaded_avatar_id AS flagger_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = pa.user_id
),
staff_data AS (
    SELECT pa.id,
           u.id AS staff_id,
           u.username_lower AS staff_username,
           u.uploaded_avatar_id AS staff_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id)
),
flag_types AS (
    SELECT pat.id,
           pat.id AS flag_type
    FROM post_action_types pat
)
SELECT
    CASE
        WHEN pat.flag_type = 3 THEN 'off_topic'
        WHEN pat.flag_type = 4 THEN 'inappropriate'
        WHEN pat.flag_type = 6 THEN 'notify_user'
        WHEN pat.flag_type = 7 THEN 'notify_moderators'
        WHEN pat.flag_type = 8 THEN 'spam'
    END "type",
    pa.post_id as flagged_post_id,
    pd.poster_id as poster_user_id,
    fd.flagger_id as flagger_user_id,
    DATE(pa.created_at) as flag_created,
    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'
    END "resolution",
    sd.staff_id as assigned_user_id,
    ROUND(pa.time_to_resolution_minutes,2) as "resolution_time (minutes)"
FROM period_actions pa
JOIN poster_data pd ON pd.id = pa.id
JOIN flagger_data fd ON fd.id = pa.id
LEFT JOIN staff_data sd ON sd.id = pa.id
JOIN flag_types pat ON pat.id = pa.post_action_type_id
ORDER BY pa.created_at ASC

SQLクエリの説明

クエリは、共通テーブル式（CTE）を使用して構造化されており、レポートをコンパイルするために最終的なSELECTステートメントで使用されるデータサブセットを準備します。

パラメータ

クエリは2つのパラメータを受け入れます。

  • :start_date: レポートを生成する期間の開始日。
  • :end_date: レポートを生成する期間の終了日。

CTE

period_actions
このCTEは、指定された日付範囲内にあり、特定のタイプ（3、4、6、7、8）のポストアクション（フラグ）を選択します。これらは異なるフラグ理由に対応します。フラグの作成時間と応答時間（同意、不同意、延期）との差を計算することにより、「time_to_resolution_minutes」を計算します。

poster_data
このCTEは、period_actionsをpostsおよびusersテーブルと結合して、フラグが付けられた投稿の投稿者に関する情報（ユーザーID、トピックID、投稿番号、ユーザー名など）を取得します。

flagger_data
このCTEは、period_actionsをusersテーブルと結合して、投稿にフラグを付けたユーザーに関する情報（ユーザーIDとユーザー名など）を取得します。

staff_data
このCTEは、period_actionsをusersテーブルと結合し、フラグに同意、不同意、または延期したスタッフメンバーのIDに基づいて、フラグを処理したスタッフメンバーに関する情報を取得します。

flag_types
このCTEは、post_action_typesテーブルからIDを単純に選択します。これは、最終的なSELECTステートメントでフラグのタイプを決定するために使用されます。

最終SELECT
最終的なSELECTステートメントは、すべてのCTEを組み合わせて、包括的なレポートを提示します。次の列が含まれます。

  • type: 提起されたフラグのタイプ。flag_type IDによって決定されます。
  • flagged_post_id: フラグが付けられた投稿のID。
  • poster_user_id: フラグが付けられたコンテンツを投稿したユーザーのID。
  • flagger_user_id: コンテンツにフラグを付けたユーザーのID。
  • flag_created: フラグが作成された日付。
  • resolution: フラグの解決ステータス（同意、不同意、延期）。
  • assigned_user_id: フラグを処理したスタッフメンバーのID。
  • resolution_time (minutes): フラグを解決するのにかかった時間（分単位）、小数点以下2桁に丸められます。

レポートは、フラグの作成日（pa.created_at）を昇順で並べ替えられます。

結果例

type flagged_post poster_user flagger_user flag_created resolution assigned_user resolution_time (minutes)
off_topic Example_Post_Link1 example_user1 example_user2 2023-01-01 agreed example_user3 4.56
inappropriate Example_Post_Link2 example_user1 example_user2 2023-01-02 disagreed example_user3 38.76
spam Example_Post_Link3 example_user1 example_user2 2023-01-03 deferred example_user3 79.32
「いいね！」 1