ダッシュボードレポート - 無視/ミュートされたトップユーザー

[SaraDev (Sara Devlaeminck)]

これは、トップの無視/ミュートされたユーザーのダッシュボードレポートのSQLバージョンです。

このダッシュボードレポートは、管理者に、指定された開始日と終了日の間にプラットフォームのどのユーザーが他のメンバーによって無視またはミュートされたかについての洞察を提供します。無視されたユーザーとは、メンバーによって選択的に除外され、その投稿がメンバーに見えないようにされたユーザーであり、ミュートされたユーザーとは、メンバーが通知を受け取らないことを選択したユーザーです。

このレポートは、コミュニティ内で摩擦を引き起こしているユーザー、またはその行動が他のメンバーのエンゲージメントを低下させているユーザーを特定できます。レポートは、頻繁に無視またはミュートされたユーザーに連絡して行動に対処するなどのモデレーションの決定に役立つか、コミュニティ全体で注意が必要な問題があるかどうかをより広範に理解するのに役立ちます。

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-01

WITH ignored_users AS (
    SELECT
        ignored_user_id AS user_id,
        COUNT(*) AS ignores_count
    FROM ignored_users
    WHERE created_at >= :start_date
      AND created_at <= :end_date
    GROUP BY ignored_user_id
    ORDER BY COUNT(*) DESC
),
muted_users AS (
    SELECT
        muted_user_id AS user_id,
        COUNT(*) AS mutes_count
    FROM muted_users
    WHERE created_at >= :start_date
      AND created_at <= :end_date
    GROUP BY muted_user_id
    ORDER BY COUNT(*) DESC
)

SELECT
    u.id AS user_id,
    u.username AS username,
    ig.ignores_count AS ignores_count,
    COALESCE(mu.mutes_count, 0) AS mutes_count,
    ig.ignores_count + COALESCE(mu.mutes_count, 0) AS total
FROM users AS u
JOIN ignored_users AS ig ON ig.user_id = u.id
LEFT OUTER JOIN muted_users AS mu ON mu.user_id = u.id
ORDER BY total DESC

SQLクエリの説明

このSQLクエリはいくつかのステップで機能します。

• 日付パラメータ:
  • クエリは、レポートの日付範囲を定義する :start_date と :end_date の2つのパラメータを受け入れます。両方の日付パラメータは YYYY-MM-DD の日付形式を受け入れます。
• 共通テーブル式 (CTE):
  • ignored_users CTEは ignored_user_id を選択し、そのIDが ignored_users テーブルに現れる回数（ユーザーが無視された回数を示す）をカウントし、結果を :start_date と :end_date の間でフィルタリングします。
  • muted_users CTEは同様ですが、muted_users テーブルから muted_user_id を選択し、日付フィルタ内でインスタンスをカウントします。
• メインSELECTステートメント: users テーブルからユーザーの詳細と、ignored_users および muted_users CTEからのカウントを選択します。
  • ignores_count は ignored_users CTEから直接取得されます。
  • mutes_count は muted_users CTEから取得されますが、NULL（ユーザーがミュートされていないことを意味する）の場合は COALESCE 関数を使用して0に置き換えられます。
  • 合計は、無視とミュートを合計して計算されます。
• JOIN: メインクエリは ignored_users との JOIN を利用して、少なくとも1回無視されたすべてのユーザーを含め、muted_users との LEFT OUTER JOIN を利用して、ミュートされていない可能性のあるユーザーも含めます。
• ORDER BY: レポートは、合計の無視とミュートの数に基づいて降順に並べ替えられ、最も「問題のある」ユーザーがレポートの上部に表示されます。

サンプル結果

user	username	ignores_count	mutes_count	total
user1	user1	4	1	5
user2	user2	3	0	3
user3	user3	1	2	3