تقرير لوحة القيادة - نشاط المشرف

[SaraDev (Sara Devlaeminck)]

هذا إصدار SQL من تقرير لوحة المعلومات لنشاط المشرف.

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

يعد تقرير لوحة المعلومات هذا أداة قيمة للمسؤولين الذين يتطلعون إلى قياس فعالية وتفاعل فريق الإشراف الخاص بهم، مما يوفر نظرة مفصلة على نشاطهم ومساهماتهم. يمكن أن تستند الأفكار التي يقدمها التقرير إلى قرارات بشأن تدريب المشرفين والاعتراف بهم وتوظيفهم، وضمان أن فريق الإشراف متوازن وفعال في الحفاظ على معايير المجتمع.

--[params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false


WITH mods AS (
    SELECT
        id AS user_id,
        username_lower AS username,
        uploaded_avatar_id
    FROM users u
    WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
    AND u.id > 0

),

time_read AS (
    SELECT SUM(uv.time_read) AS time_read,
        uv.user_id
    FROM mods m
    JOIN user_visits uv ON m.user_id = uv.user_id
    WHERE uv.visited_at >= :start_date
        AND uv.visited_at <= :end_date
    GROUP BY uv.user_id
),
      
flag_count AS (
    WITH period_actions AS (
        SELECT agreed_by_id,
        disagreed_by_id
        FROM post_actions
        WHERE post_action_type_id IN (3,4,8,6,7)
        AND created_at >= :start_date
        AND created_at <= :end_date
    ),

agreed_flags AS (
        SELECT pa.agreed_by_id AS user_id,
        COUNT(*) AS flag_count
        FROM mods m
        JOIN period_actions pa
        ON pa.agreed_by_id = m.user_id
        GROUP BY agreed_by_id
    ),

disagreed_flags AS (
        SELECT pa.disagreed_by_id AS user_id,
        COUNT(*) AS flag_count
        FROM mods m
        JOIN period_actions pa
        ON pa.disagreed_by_id = m.user_id
        GROUP BY disagreed_by_id
    )
    SELECT
    COALESCE(af.user_id, df.user_id) AS user_id,
    COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
    FROM agreed_flags af
    FULL OUTER JOIN disagreed_flags df
    ON df.user_id = af.user_id
      ),
      
revision_count AS (
      SELECT pr.user_id,
      COUNT(*) AS revision_count
      FROM mods m
      JOIN post_revisions pr
      ON pr.user_id = m.user_id
      JOIN posts p
      ON p.id = pr.post_id
      WHERE pr.created_at >= :start_date
      AND pr.created_at <= :end_date
      AND p.user_id <> pr.user_id
      GROUP BY pr.user_id
),
      
topic_count AS (
      SELECT t.user_id,
        COUNT(*) AS topic_count
      FROM mods m
      JOIN topics t ON t.user_id = m.user_id
      WHERE t.archetype = 'regular'
          AND t.created_at >= :start_date
          AND t.created_at <= :end_date
      GROUP BY t.user_id
),
      
post_count AS (
      SELECT p.user_id,
         COUNT(*) AS post_count
      FROM mods m
      JOIN posts p ON p.user_id = m.user_id
      JOIN topics t ON t.id = p.topic_id
      WHERE t.archetype = 'regular'
          AND p.created_at >= :start_date
          AND p.created_at <= :end_date
      GROUP BY p.user_id
),
      
pm_count AS (
      SELECT p.user_id,
        COUNT(*) AS pm_count
      FROM mods m
      JOIN posts p ON p.user_id = m.user_id
      JOIN topics t ON t.id = p.topic_id
      WHERE t.archetype = 'private_message'
          AND p.created_at >= :start_date
          AND p.created_at <= :end_date
      GROUP BY p.user_id
      )
      
SELECT
    m.user_id,
    m.username,
    fc.flag_count as flags_reviewed,
    ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
    tc.topic_count as topics_created,
    pmc.pm_count as PMs_created,
    pc.post_count as posts_created,
    rc.revision_count as revisions
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
ORDER BY m.username ASC

المعلمات

• :start_date و :end_date - تحدد هذه المعلمات النطاق الزمني للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.
• :include_admins - تحدد هذه المعلمة ما إذا كان سيتم تضمين المسؤولين في التقرير إلى جانب المشرفين.

شرح استعلام SQL

يتم تنظيم التقرير باستخدام تعبيرات الجدول المشتركة (CTEs) لتقسيم معالجة البيانات إلى أقسام قابلة للإدارة ومنطقية. إليك ما يحدث في كل CTE:

1. mods: يحدد جميع المستخدمين الذين لديهم حالة مشرف أو حالة مسؤول (إذا تم تضمينهم بواسطة المعلمة :include_admins). يختار فقط أعمدة المستخدم ذات الصلة للاستعلامات الإضافية.
2. time_read: يحسب إجمالي الوقت (بالثواني) الذي قضاه كل مشرف في قراءة المحتوى على المنصة بين تواريخ البدء والانتهاء المقدمة.
3. flag_count: يحسب عدد العلامات التي وافق عليها المشرفون أو لم يوافقوا عليها خلال الفترة المحددة. يأخذ في الاعتبار أنواع علامات متعددة ممثلة بمعرفات إجراءات المشاركة الخاصة بها.
4. revision_count: يحسب عدد مراجعات المشاركات التي أجراها المشرفون على مشاركات المستخدمين الآخرين ضمن الإطار الزمني المحدد.
5. topic_count: يحسب عدد المواضيع العادية التي أنشأها المشرفون.
6. post_count: يحسب عدد المشاركات التي أنشأها المشرفون في المواضيع العادية.
7. pm_count: يحسب عدد الرسائل الخاصة التي بدأها المشرفون.

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

نتائج مثال

user	username	flags_reviewed	time_reading_hours	topics_created	pms_created	posts_created	revisions
1	moderator1	NULL	36.11	NULL	344	8	15
2	moderator2	46	104.52	2	271	466	363
3	moderator3	NULL	72.15	NULL	418	64	16