تقرير كفاءة معالجة بلاغات المشرفين

البيانات والتقارير
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) -- Flag types
      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

المعلمات (Parameters)

يستخدم الاستعلام معلمتين لتحديد النطاق الزمني الذي يتم تحليل البيانات له:

  • :start_date: تاريخ بدء الفترة التي تريد تحليلها، بتنسيق YYYY-MM-DD.
  • :end_date: تاريخ انتهاء الفترة التي تريد تحليلها، أيضًا بتنسيق YYYY-MM-DD.

شرح عبارات الجداول المشتركة (CTEs Explained)

  • period_actions: تحدد عبارة الجدول المشتركة هذه الإبلاغات التي تم رفعها خلال الفترة الزمنية المحددة، مع التركيز على أنواع معينة من الإبلاغات. تقوم بحساب الوقت المستغرق لحل كل إبلاغ (بالدقائق) من لحظة إنشائه حتى تمت الموافقة عليه أو رفضه أو تأجيله من قبل مشرف.
  • moderator_actions: تقوم عبارة الجدول المشتركة هذه بتصفية الإجراءات من period_actions لتشمل فقط تلك التي تم الرد عليها من قبل مشرف. تحدد المشرف المسؤول عن كل إجراء.
  • moderator_stats: تقوم عبارة الجدول المشتركة هذه بتجميع البيانات من moderator_actions، وحساب العدد الإجمالي للإبلاغات التي تعامل معها كل مشرف ومتوسط وقت الحل (بالدقائق) للإبلاغات التي تعامل معها.

النتائج (Results)

يوفر الناتج النهائي للاستعلام قائمة بالمشرفين مرتبة حسب عدد الإبلاغات التي تعاملوا معها، مع ترتيب ثانوي حسب متوسط وقت الحل الخاص بهم (بترتيب تصاعدي). لكل مشرف، يعرض التقرير:

  • moderator_username: اسم المستخدم الخاص بالمشرف.
  • handled_flags: العدد الإجمالي للإبلاغات التي تعامل معها المشرف خلال الفترة الزمنية المحددة.
  • avg_resolution_time_minutes: متوسط الوقت الذي استغرقه المشرف لحل الإبلاغ، مقربًا إلى منزلتين عشريتين.

مثال للنتائج (Example Results)

moderator_username handled_flags avg_resolution_time_minutes
Moderator1 343 39.41
Moderator2 70 30.51
Moderator3 63 42.21