تقرير لوحة التحكم - حالة العلامات

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

تم تنظيم الاستعلام باستخدام تعبيرات الجدول المشتركة (CTEs) التي تعد مجموعة فرعية من البيانات التي يتم استخدامها بعد ذلك في عبارة SELECT النهائية لتجميع التقرير.

المعلمات

يقبل الاستعلام معلمتين:

  • :start_date: بداية الفترة الزمنية التي سيتم إنشاء التقرير لها.
  • :end_date: نهاية الفترة الزمنية التي سيتم إنشاء التقرير لها.

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

period_actions

تحدد تعبيرات الجدول المشتركة هذه إجراءات المشاركات (العلامات) التي تقع ضمن النطاق الزمني المحدد وهي من أنواع محددة (3، 4، 6، 7، 8)، والتي تتوافق مع أسباب مختلفة للعلامات. تقوم بحساب ‘time_to_resolution_minutes’ عن طريق إيجاد الفرق بين وقت إنشاء العلامة والوقت الذي تم الرد عليها فيه (الموافقة، الرفض، أو التأجيل).

poster_data

تقوم تعبيرات الجدول المشتركة هذه بربط period_actions مع جداول posts و users لاسترداد معلومات حول ناشر المشاركة التي تم وضع علامة عليها، بما في ذلك معرف المستخدم الخاص به، ومعرف الموضوع، ورقم المشاركة، واسم المستخدم.

flagger_data

تقوم تعبيرات الجدول المشتركة هذه بربط period_actions مع جدول users للحصول على معلومات حول المستخدم الذي وضع علامة على المشاركة، بما في ذلك معرف المستخدم واسم المستخدم الخاص به.

staff_data

تسترد تعبيرات الجدول المشتركة هذه معلومات حول عضو الفريق الذي تعامل مع العلامة عن طريق ربط period_actions بجدول users بناءً على معرفات أعضاء الفريق الذين وافقوا على العلامة أو رفضوها أو أجلواها.

flag_types

تقوم تعبيرات الجدول المشتركة هذه ببساطة بتحديد المعرفات من جدول post_action_types، والتي سيتم استخدامها لتحديد نوع العلامة في عبارة SELECT النهائية.

SELECT النهائية

تجمع عبارة SELECT النهائية جميع تعبيرات الجدول المشتركة لتقديم تقرير شامل. وهي تتضمن الأعمدة التالية:

  • type: نوع العلامة التي تم رفعها، والتي يتم تحديدها بواسطة معرف flag_type.
  • flagged_post_id: معرف المشاركة التي تم وضع علامة عليها.
  • poster_user_id: معرف المستخدم الذي نشر المحتوى الذي تم وضع علامة عليه.
  • flagger_user_id: معرف المستخدم الذي وضع علامة على المحتوى.
  • flag_created: التاريخ الذي تم فيه إنشاء العلامة.
  • resolution: حالة حل العلامة (موافق عليها، مرفوضة، مؤجلة).
  • assigned_user_id: معرف عضو الفريق الذي تعامل مع العلامة.
  • resolution_time (minutes): الوقت المستغرق لحل العلامة، بالدقائق، مقربًا إلى منزلتين عشريتين.

ثم يتم ترتيب التقرير حسب تاريخ إنشاء العلامة (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)