ダッシュボードレポート - ユーザーフラグ比率

データとレポート
, ,
1

これは、ユーザーフラグ比率のダッシュボードレポートのSQLバージョンです。

このダッシュボードレポートは、スタッフのフラグ（同意および不同意）に対する応答の比率で並べられたユーザーのリストを生成します。レポートには、各ユーザーのフラグアクティビティに関する情報が含まれており、特にモデレーターによって同意された、不同意された、または無視されたフラグの数、およびユーザーのフラグパフォーマンスを表す計算されたスコアに焦点を当てています。

このレポートは、管理者が不適切なコンテンツを正確にフラグ付けするユーザーを特定し、コミュニティモデレーションにおけるユーザーエンゲージメントを測定し、フラグ付けの実践に関するフィードバックを提供し、潜在的なモデレーターロールのためのユーザーフラグパフォーマンスを評価するのに役立ちます。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01

SELECT
  u.id AS user_id,
  u.username,
  CASE
    WHEN u.silenced_till IS NOT NULL THEN 't'
    ELSE 'f'
  END AS silenced,
  SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric AS disagreed_flags,
  SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric AS agreed_flags,
  SUM(CASE WHEN rs.status = 3 THEN 1 ELSE 0 END)::numeric AS ignored_flags,
  (
    CASE
      WHEN SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric = 0 THEN
        SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric * SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric
      ELSE
        ROUND(
          (1 - (SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric / SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END))) *
          (SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) - SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric)
        )
    END
  ) AS score
FROM
  users AS u
  INNER JOIN reviewable_scores AS rs ON rs.user_id = u.id
WHERE
  u.id > 0
  AND rs.created_at >= :start_date
  AND rs.created_at <= :end_date
GROUP BY
  u.id,
  u.username,
  u.uploaded_avatar_id,
  u.silenced_till
ORDER BY
  score DESC
LIMIT 100

SQLクエリの説明

クエリの概要は次のとおりです。

  • パラメータ: クエリは、ユーザーがレポートの日付範囲を指定できる2つのパラメータ、:start_dateと:end_dateを受け入れます。両方のdateパラメータはYYYY-MM-DDの日付形式を受け入れます。
  • SELECT句: クエリは次の列を選択します。
    • user_id: ユーザーの一意の識別子。
    • username: ユーザーのユーザー名。
    • silenced: ユーザーが現在ミュートされているかどうかを示すブール値。
    • disagreed_flags: モデレーターによって不同意とされたユーザーフラグの総数。
    • agreed_flags: モデレーターによって同意されたユーザーフラグの総数。
    • ignored_flags: モデレーターによって無視されたユーザーフラグの総数。
    • score: ユーザーのフラグパフォーマンスを表す計算されたスコア。
  • FROM句: クエリは、各ユーザーに関連するフラグ情報を取得するために、usersテーブルとreviewable_scoresテーブルをuser_idで結合します。
  • WHERE句: クエリは、idが0より大きいユーザーのレコードと、reviewable_scoresのcreated_at日付が指定された日付範囲内にあるレコードのみを含めるようにデータをフィルタリングします。
  • GROUP BY句: クエリは、結果をuser_id、username、uploaded_avatar_id、およびsilenced_tillでグループ化して、各ユーザーのフラグデータを集計します。
  • ORDER BY句: クエリは、計算されたscoreで結果を降順に並べ替え、最も高いフラグパフォーマンスを持つユーザーを最初に表示します。
  • LIMIT句: クエリは、計算されたスコアに基づいて上位100人のユーザーに結果を制限します。

結果例

user username silenced disagreed_flags agreed_flags ignored_flags score
user_1_id user_1 f 0.0 10.0 3.0 100.0
user_2_id user_2 f 0.0 6.0 3.0 36.0
user_3_id user_3 f 0.0 4.0 0.0 16.0
… … … … … … …
「いいね！」 1