تقرير لوحة التحكم - نسبة تبليغ المستخدمين

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

فيما يلي تفصيل للاستعلام:

  • المعلمات: يقبل الاستعلام معلمتين، :start_date و :end_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 وحيث يكون تاريخ 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)