تحليل تقارير أنشطة المراجعة والإبلاغ عن المخالفات

SaraDev · 13 فبراير 2025، 11:08م

الحفاظ على مجتمع صحي وشامل يتطلب إدارة فعالة، والتي يمكن أن تشمل مراجعة المنشورات المُبلغ عنها، وتحليل أداء المشرفين، وإدارة محتوى المستخدمين.

تحتوي هذه الدليل على مجموعة متنوعة من تقارير SQL لـ Discourse مصممة لمساعدة في تحليل الأنشطة المتعلقة بالإدارة.

في هذا الموضوع ستجد استعلامات Data Explorer مفصلة لـ:

إحصائيات حل البلاغات.
نسب حل العناصر القابلة للمراجعة.
مقاييس أداء محددة للمشرفين.
رؤى حول نشاط المستخدمين في الإبلاغ.
بيانات شاملة لجميع الإجراءات المتعلقة بالمستخدمين والمنشورات والمواضيع المُبلغ عنها

نسبة حل بلاغات المنشورات حسب النوع

شرح استعلام SQL

يحسب هذا الاستعلام نسبة الحلول (موافق، غير موافق، مؤجل، محذوف) للمنشورات المُبلغ عنها، مجمعة حسب نوع البلاغ.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-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.deleted_at,
           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 -- الوقت اللازم للحل بالدقائق
    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
),
flag_types AS (
    SELECT pat.id,
           CASE 
               WHEN pat.id = 3 THEN 'off_topic'
               WHEN pat.id = 4 THEN 'inappropriate'
               WHEN pat.id = 6 THEN 'notify_user'
               WHEN pat.id = 7 THEN 'notify_moderators'
               WHEN pat.id = 8 THEN 'spam'
           END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT
        pa.post_action_type_id,
        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'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.post_action_type_id
),
resolution_percentages AS (
    SELECT
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT
        flag_type,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_count ELSE 0 END) AS deleted_count,
        MAX(total_flags) AS total_flags
    FROM resolution_percentages
    GROUP BY flag_type
)
SELECT
    flag_type,
    agreed_percentage,
    agreed_count,
    disagreed_percentage,
    disagreed_count,
    deferred_percentage,
    deferred_count,
    deleted_percentage,
    deleted_count,
    total_flags
FROM pivoted_data
ORDER BY flag_type

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية المنشورات المُبلغ عنها.
:end_date: تاريخ الانتهاء لتصفية المنشورات المُبلغ عنها.

شرح الجداول المؤقتة (CTEs)

period_actions: يصفّي المنشورات المُبلغ عنها ضمن النطاق الزمني المحدد ويحسب الوقت اللازم لحل كل بلاغ.
flag_types: يربط معرفات أنواع البلاغات بأسماء قابلة للقراءة البشرية (مثل: خارج الموضوع، غير لائق، إعلانات مزعجة).
flag_resolutions: يجمع البلاغات حسب النوع والحل (موافق، غير موافق، مؤجل، محذوف) ويحسب عدد حدوث كل حل.
flag_totals: يحسب العدد الإجمالي للبلاغات لكل نوع بلاغ.
resolution_percentages: يجمع بين أعداد الحلول والإجماليات لحساب نسبة كل نوع حل لكل نوع بلاغ.
pivoted_data: يعيد تشكيل البيانات لعرض نسب الحلول وأعدادها في أعمدة منفصلة لكل نوع حل.

شرح النتائج

النتيجة النهائية هي جدول يعرض:

نوع البلاغ (مثل: خارج الموضوع، إعلانات مزعجة).
النسب المئوية والأعداد لكل نوع حل (موافق، غير موافق، مؤجل، محذوف).
إجمالي البلاغات لكل نوع بلاغ.

نتائج مثال

نوع البلاغ	نسبة الموافقة	عدد الموافقات	نسبة الرفض	عدد الرفض	نسبة التأجيل	عدد التأجيلات	نسبة الحذف	عدد الحذف	إجمالي البلاغات
off_topic	50.00	25	30.00	15	10.00	5	10.00	5	50
spam	70.00	35	20.00	10	5.00	2	5.00	3	50

نسب حل العناصر القابلة للمراجعة

شرح استعلام SQL

يحلل هذا الاستعلام حالات حل العناصر القابلة للمراجعة (مثل المنشورات المُبلغ عنها) ضمن نطاق زمني معين. يحسب النسبة المئوية وعدد كل حالة حل لكل نوع بلاغ.

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

WITH flag_data AS (
    SELECT
        r.type AS flag_type,
        r.status AS resolution_status,
        COUNT(*) AS flag_count
    FROM
        reviewables r
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        r.type,
        r.status
),
flag_totals AS (
    SELECT
        flag_type,
        SUM(flag_count) AS total_flags
    FROM
        flag_data
    GROUP BY
        flag_type
),
flag_percentages AS (
    SELECT
        fd.flag_type,
        fd.resolution_status,
        fd.flag_count,
        ROUND((fd.flag_count::decimal / ft.total_flags) * 100, 2) AS percentage
    FROM
        flag_data fd
    JOIN
        flag_totals ft
    ON
        fd.flag_type = ft.flag_type
)
SELECT
    fp.flag_type,
    -- النسب المئوية
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.percentage END), 0) AS pending_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.percentage END), 0) AS approved_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.percentage END), 0) AS rejected_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.percentage END), 0) AS ignored_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.percentage END), 0) AS deleted_percentage,
    -- الأعداد
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.flag_count END), 0) AS pending_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.flag_count END), 0) AS approved_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.flag_count END), 0) AS rejected_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.flag_count END), 0) AS ignored_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.flag_count END), 0) AS deleted_count
FROM
    flag_percentages fp
GROUP BY
    fp.flag_type
ORDER BY
    fp.flag_type

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية العناصر القابلة للمراجعة.
:end_date: تاريخ الانتهاء لتصفية العناصر القابلة للمراجعة.

شرح الجداول المؤقتة (CTEs)

flag_data: يجمع العناصر القابلة للمراجعة حسب نوع البلاغ وحالة الحل، ويحسب عدد حدوث كل مجموعة.
flag_totals: يحسب العدد الإجمالي للبلاغات لكل نوع بلاغ.
flag_percentages: يجمع بين أعداد البلاغات والإجماليات لحساب نسبة كل حالة حل لكل نوع بلاغ.

شرح النتائج

النتيجة النهائية هي جدول يعرض:

نوع البلاغ.
النسب المئوية والأعداد لكل حالة حل (قيد الانتظار، معتمد، مرفوض، متجاهل، محذوف).

نتائج مثال

نوع البلاغ	نسبة الانتظار	عدد الانتظار	نسبة الاعتماد	عدد الاعتماد	نسبة الرفض	عدد الرفض	نسبة التجاهل	عدد التجاهل	نسبة الحذف	عدد الحذف
off_topic	20.00	10	50.00	25	10.00	5	10.00	5	10.00	5
spam	10.00	5	70.00	35	10.00	5	5.00	2	5.00	3

حلول بلاغات المشرفين

شرح استعلام SQL

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

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.deleted_at,
        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 -- الوقت اللازم للحل بالدقائق
    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
),
flag_types AS (
    SELECT 
        pat.id,
        CASE 
            WHEN pat.id = 3 THEN 'off_topic'
            WHEN pat.id = 4 THEN 'inappropriate'
            WHEN pat.id = 6 THEN 'notify_user'
            WHEN pat.id = 7 THEN 'notify_moderators'
            WHEN pat.id = 8 THEN 'spam'
        END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        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'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id
),
resolution_percentages AS (
    SELECT 
        fr.user_id,
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.user_id = fr.user_id AND ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT 
        rp.user_id,
        rp.flag_type,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_count ELSE 0 END) AS deleted_count,
        MAX(rp.total_flags) AS total_flags
    FROM resolution_percentages rp
    GROUP BY rp.user_id, rp.flag_type
)
SELECT 
    u.id AS user_id,
    u.username,
    p.flag_type,
    p.agreed_percentage,
    p.agreed_count,
    p.disagreed_percentage,
    p.disagreed_count,
    p.deferred_percentage,
    p.deferred_count,
    p.deleted_percentage,
    p.deleted_count,
    p.total_flags
FROM pivoted_data p
JOIN users u ON u.id = p.user_id
WHERE (:only_staff = false OR (u.admin = true OR u.moderator = true))
ORDER BY u.username, p.flag_type, p.total_flags

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية المنشورات المُبلغ عنها.
:end_date: تاريخ الانتهاء لتصفية المنشورات المُبلغ عنها.
:only_staff: معلمة منطقية لتصفية النتائج لتشمل فقط أعضاء الفريق الإداري.

شرح الجداول المؤقتة (CTEs)

period_actions: يصفّي المنشورات المُبلغ عنها ضمن النطاق الزمني المحدد ويحسب الوقت اللازم لحل كل بلاغ.
flag_types: يربط معرفات أنواع البلاغات بأسماء قابلة للقراءة البشرية.
flag_resolutions: يجمع البلاغات حسب المستخدم، ونوع البلاغ، والحل، ويحسب عدد حدوث كل مجموعة.
flag_totals: يحسب العدد الإجمالي للبلاغات لكل مستخدم ونوع بلاغ.
resolution_percentages: يجمع بين أعداد الحلول والإجماليات لحساب النسب المئوية لكل نوع حل.
pivoted_data: يعيد تشكيل البيانات لعرض نسب الحلول وأعدادها في أعمدة منفصلة لكل نوع حل.

شرح النتائج

النتيجة النهائية هي جدول يعرض:

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

نتائج مثال

المشرف	نوع البلاغ	نسبة الموافقة	عدد الموافقات	نسبة الرفض	عدد الرفض	نسبة التأجيل	عدد التأجيلات	نسبة الحذف	عدد الحذف	إجمالي البلاغات
mod1	off_topic	60.00	30	20.00	10	10.00	5	10.00	5	50
mod2	spam	70.00	35	20.00	10	5.00	2	5.00	3	50

من يقوم بالإبلاغ عن المنشورات

شرح استعلام SQL

يحدد هذا الاستعلام المستخدمين الذين أبلغوا عن منشورات ضمن نطاق زمني محدد ويحسب العدد الإجمالي للبلاغات المقدمة من كل مستخدم.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

SELECT 
    u.id AS user_id,
    u.username,
    COUNT(pa.id) AS flag_count
FROM post_actions pa
JOIN users u ON u.id = pa.user_id
WHERE pa.post_action_type_id IN (3, 4, 6, 7, 8) -- أنواع البلاغات
  AND pa.created_at >= :start_date
  AND pa.created_at <= :end_date
  AND (:only_staff = false OR (u.admin = true OR u.moderator = true))
GROUP BY u.id, u.username
ORDER BY flag_count DESC, u.username
LIMIT 10

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية المنشورات المُبلغ عنها.
:end_date: تاريخ الانتهاء لتصفية المنشورات المُبلغ عنها.
:only_staff: معلمة منطقية لتصفية النتائج لتشمل فقط أعضاء الفريق الإداري.

شرح النتائج

النتيجة النهائية هي قائمة مرتبة للمستخدمين مع أعداد بلاغاتهم.

نتائج مثال

معرف المستخدم	اسم المستخدم	عدد البلاغات
1	user1	50
2	user2	30
3	user3	20

ملاحظات المستخدمين

شرح استعلام SQL

يجلب هذا الاستعلام ملاحظات المستخدمين المخزنة في جدول plugin_store_rows. يستخرج تفاصيل مثل معرف المستخدم، وتاريخ الإنشاء، ومحتوى الملاحظة، ومعرف الشخص الذي أنشأها.

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

WITH user_notes AS (

    SELECT 
        REPLACE(key, 'notes:', '')::int AS user_id,
        notes.value->>'created_at' AS created_at,
        notes.value->>'raw' AS user_note,
        notes.value->>'created_by' AS created_by
    FROM plugin_store_rows,
    LATERAL json_array_elements(value::json) notes
    WHERE plugin_name = 'user_notes'
    ORDER BY 2 DESC 
)

SELECT 
    un.user_id,
    un.created_at::date,
    un.user_note,
    un.created_by AS created_by_user_id
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at::date BETWEEN :start_date AND :end_date
ORDER BY created_at DESC

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية ملاحظات المستخدمين.
:end_date: تاريخ الانتهاء لتصفية ملاحظات المستخدمين.

شرح النتائج

النتيجة النهائية هي قائمة مفصلة بملاحظات المستخدمين مع البيانات الوصفية ذات الصلة.

نتائج مثال

معرف المستخدم	تاريخ الإنشاء	ملاحظة المستخدم	معرف المستخدم المنشئ
1	2025-01-01	هذا المستخدم مفيد.	2
2	2025-02-01	هذا المستخدم مرتبط بحسابين مستخدمين آخرين.	3

مؤشرات أداء رئيسية للمشرفين - البلاغات ومتوسط وقت حل البلاغ

شرح استعلام SQL

يقيّم هذا الاستعلام أداء المشرفين من خلال حساب عدد البلاغات التي تعاملوا معها ومتوسط وقت الحل (بالدقائق) لكل مشرف.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-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 -- الوقت اللازم للحل بالدقائق
    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
),
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

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية المنشورات المُبلغ عنها.
:end_date: تاريخ الانتهاء لتصفية المنشورات المُبلغ عنها.

شرح الجداول المؤقتة (CTEs)

period_actions: يصفّي المنشورات المُبلغ عنها ضمن النطاق الزمني المحدد ويحسب الوقت اللازم لحل كل بلاغ.
moderator_actions: يحدد البلاغات التي حلها المشرفون ويحسب الوقت اللازم لحل كل بلاغ.
moderator_stats: يجمع البلاغات حسب المشرف ويحسب العدد الإجمالي للبلاغات التي تعامل معها ومتوسط وقت الحل.

شرح النتائج

النتيجة النهائية هي قائمة مرتبة للمشرفين مع أعداد البلاغات التي تعاملوا معها وأوقات الحل المتوسطة.

نتائج مثال

اسم مستخدم المشرف	البلاغات المعالجة	متوسط وقت الحل (بالدقائق)
mod1	50	15.00
mod2	30	20.00

جميع بيانات البلاغات

شرح استعلام SQL

يوفر هذا الاستعلام مجموعة بيانات شاملة لجميع بيانات المستخدمين والمنشورات والمواضيع المُبلغ عنها ضمن نطاق زمني محدد. يجمع البيانات من جداول متعددة لتشمل تفاصيل مثل نوع البلاغ، والعنصر المُبلغ عنه، وسبب البلاغ، ومصدر البلاغ، وقرار الحل، والرسائل ذات الصلة.

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        r.potentially_illegal AS potentially_illegal, 
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        p.hidden_at AS post_hidden_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        --AND r.status = 1 -- تضمين فقط البلاغات التي تم الموافقة عليها واتخاذ إجراء
),
review_decisions AS (
    SELECT
        0 AS status_code, 'pending' AS decision_name
    UNION ALL
    SELECT
        1 AS status_code, 'agreed' AS decision_name
    UNION ALL
    SELECT
        2 AS status_code, 'disagreed' AS decision_name
    UNION ALL
    SELECT
        3 AS status_code, 'ignored' AS decision_name
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'off_topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'illegal' AS flag_type_name
)
SELECT
    fd.flag_id,
    fd.post_id AS flagged_item,
    fd.flagged_by_username,
    fd.flagged_date,
    fd.flag_type,
    ft.flag_type_name AS flag_type_name,
    fd.flag_source AS reviewable_by_moderator,
    fd.flag_reason,
    fd.flagged_item_text,
    pa.related_post_id AS related_message_id_post_id,
    regexp_replace(rp.raw, '(https?://[^\s]+)', '', 'g') AS related_message_text, -- إزالة الروابط فقط
    fd.reviewed_at,
    fd.reviewed_by_username AS reviewed_by,
    rd.decision_name AS review_decision,
    CASE 
        WHEN fd.user_silenced_till IS NOT NULL THEN 'User silenced'
        WHEN fd.user_suspended_till IS NOT NULL THEN 'User suspended'
        WHEN fd.post_deleted_at IS NOT NULL THEN 'Post deleted'
        WHEN fd.post_hidden_at IS NOT NULL THEN 'Post hidden'
        ELSE 'No action taken'
    END AS action_taken,
    CASE 
        WHEN fd.reviewed_at IS NOT NULL THEN ROUND(EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date)) / 60, 2)
        ELSE NULL
    END AS review_time_minutes -- فرق الوقت بالدقائق
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN posts rp 
    ON pa.related_post_id = rp.id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
LEFT JOIN review_decisions rd 
    ON fd.review_status = rd.status_code
ORDER BY
    fd.flagged_date DESC

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية المنشورات المُبلغ عنها.
:end_date: تاريخ الانتهاء لتصفية المنشورات المُبلغ عنها.

شرح الجداول المؤقتة (CTEs)

flag_data: يسترجع معلومات مفصلة عن المنشورات المُبلغ عنها، بما في ذلك معرف البلاغ، والعنصر المُبلغ عنه، ونوع البلاغ، وسبب البلاغ، ومصدر البلاغ، وتفاصيل المراجعة.
review_decisions: يربط رموز حالات المراجعة بأسماء قرارات قابلة للقراءة البشرية (مثل: قيد الانتظار، موافق، غير موافق، متجاهل).
flag_types: يربط معرفات أنواع إجراءات المنشورات بأسماء أنواع البلاغات القابلة للقراءة البشرية (مثل: خارج الموضوع، غير لائق، إعلانات مزعجة).

شرح النتائج

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

نتائج مثال (مجهولة)

معرف البلاغ	العنصر المُبلغ عنه	اسم المستخدم المُبلغ	تاريخ البلاغ	نوع البلاغ	اسم نوع البلاغ	قابل للمراجعة بواسطة المشرف	سبب البلاغ	نص العنصر المُبلغ عنه	معرف الرسالة ذات الصلة	نص الرسالة ذات الصلة	اسم المستخدم المُراجع	قرار المراجعة	الإجراء المتخذ	وقت المراجعة (بالدقائق)
12345	67890	user123	2025-04-01 12:00	8	Spam	true	محتوى إعلانات مزعجة	“اشترِ الآن من spam.com”	98765	“تحقق من هذا!”	mod456	موافق	تم حذف المنشور	15.25
12346	67891	user124	2025-04-02 14:30	4	غير لائق	false	مسئ	“هذا غير لائق!”	NULL	NULL	mod457	غير موافق	لم يتم اتخاذ أي إجراء	30.50

تقارير البلاغات المقدمة

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

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
    UNION ALL
    SELECT
        NULL AS post_action_type_id, 'Something else' AS flag_type_name
)
SELECT
    ft.flag_type_name AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
GROUP BY
    ft.flag_type_name
ORDER BY
    Total DESC

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية المنشورات المُبلغ عنها.
:end_date: تاريخ الانتهاء لتصفية المنشورات المُبلغ عنها.

شرح CTEs

flag_data:
تسترجع هذه CTE معلومات مفصلة حول المنشورات المُبلغ عنها، بما في ذلك:

المعرف الفريد للإبلاغ (flag_id)، ومعرف المنشور المُبلغ عنه (post_id)، والموضوع الذي ينتمي إليه (topic_id).
معلومات حول الإبلاغ نفسه، مثل المستخدم الذي قدم الإبلاغ (flagged_by_username)، وتاريخ الإبلاغ (flagged_date)، ونوع الإبلاغ (flag_type)، وسبب الإبلاغ (flag_reason).
تفاصيل حول عملية المراجعة، بما في ذلك المشرف الذي راجع الإبلاغ (reviewed_by_username)، وقرار المراجعة (review_status)، ووقت المراجعة (reviewed_at).

flag_types:
تربط هذه CTE المعرفات الرقمية لأنواع إجراءات المنشورات بأسماء أنواع الإبلاغ التي يمكن قراءتها من قبل البشر:

3: خارج الموضوع
4: غير لائق
6: إخطار المستخدم
7: إخطار المشرفين
8: محتوى غير مرغوب فيه (Spam)
10: غير قانوني
NULL: شيء آخر

شرح النتائج

يجمع الاستعلام النهائي بيانات الإبلاغ حسب نوع الإبلاغ ويوفر المقاييس التالية:

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

تم فرز النتائج حسب العدد الإجمالي للبلاغات بترتيب تنازلي.

أمثلة على النتائج

النوع	تم الإبلاغ	تلقائي	المجموع
محتوى غير مرغوب فيه	120	80	200
غير لائق	90	10	100
خارج الموضوع	60	5	65
إخطار_المشرفين	30	0	30
غير قانوني	10	2	12
شيء آخر	5	0	5

الحظر والعلّق:

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2026-01-01
SELECT 
    u.id AS user_id,
    u.username,
    u.name,
    u.suspended_at,
    u.suspended_till,
    u.silenced_till,
    u.created_at AS account_created_at,
    u.last_seen_at,
    u.flag_level,
    u.admin,
    u.moderator
FROM 
    users u
WHERE 
    (
        u.suspended_at BETWEEN :start_date AND :end_date
        OR u.silenced_till BETWEEN :start_date AND :end_date
    )
ORDER BY 
    u.suspended_at DESC NULLS LAST,
    u.silenced_till DESC NULLS LAST

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية المستخدمين المعلقين أو المكتمين.
:end_date: تاريخ الانتهاء لتصفية المستخدمين المعلقين أو المكتمين.

شرح النتائج

تسترجع هذه الاستعلام قائمة بالمستخدمين الذين تم تعليقهم أو كتم صوتهم ضمن النطاق الزمني المحدد. تتضمن الأعمدة الرئيسية:

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

تم فرز النتائج حسب تاريخ التعليق (suspended_at) وتاريخ الكتم (silenced_till) بترتيب تنازلي، مع ظهور القيم الفارغة في النهاية.

أمثلة على النتائج

معرف المستخدم	اسم المستخدم	الاسم	تاريخ التعليق	حتى تاريخ التعليق	حتى تاريخ الكتم	تاريخ إنشاء الحساب	آخر ظهور	مستوى الإبلاغ	مسؤول	مشرف
101	user123	John Doe	2025-03-15 10:00	2025-04-15 10:00	NULL	2020-01-01 12:00	2025-03-14 18:00	2	false	false
102	user456	Jane Smith	NULL	NULL	2025-03-20 18:00	2021-06-10 15:00	2025-03-19 20:00	1	false	false
103	mod789	Moderator1	2025-02-01 08:00	2025-03-01 08:00	NULL	2019-05-05 10:00	2025-01-31 22:00	3	false	true
104	admin001	AdminUser	NULL	NULL	2025-03-25 12:00	2018-12-25 09:00	2025-03-24 16:00	0	true	false

إجراءات الإبلاغ المتفق عليها

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

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
median_time_to_act AS (
    SELECT
        COALESCE(ft.flag_type_name, fd.flag_type) AS flag_type,
        ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date))) / 60) AS median_time_minutes
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        fd.reviewed_at IS NOT NULL
        AND (
            ft.flag_type_name IS NOT NULL -- Include mapped flag types
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- Include specific flag types for NULL results
        )
    GROUP BY
        COALESCE(ft.flag_type_name, fd.flag_type)
)
SELECT
    COALESCE(ft.flag_type_name, fd.flag_type) AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total,
    COALESCE(mta.median_time_minutes, 0) AS "Median time to act (minutes)",
    COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "User silenced",
    COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "User deleted",
    COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post deleted",
    COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post hidden"

FROM
    flag_data fd
LEFT JOIN flag_types ft 
    ON fd.post_action_type_id = ft.post_action_type_id
LEFT JOIN median_time_to_act mta 
    ON COALESCE(ft.flag_type_name, fd.flag_type) = mta.flag_type
WHERE
    ft.flag_type_name IS NOT NULL -- Include mapped flag types
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- Include specific flag types for NULL results
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية البلاغات المتفق عليها.
:end_date: تاريخ الانتهاء لتصفية البلاغات المتفق عليها.

شرح CTEs

flag_data:
تسترجع هذه CTE معلومات مفصلة حول البلاغات التي تم الاتفاق عليها واتخاذ إجراءات بشأنها. تتضمن:

المعرف الفريد للإبلاغ (flag_id)، ومعرف المنشور المُبلغ عنه (post_id)، والموضوع الذي ينتمي إليه (topic_id).
معلومات حول الإبلاغ نفسه، مثل المستخدم الذي قدم الإبلاغ (flagged_by_username)، وتاريخ الإبلاغ (flagged_date)، ونوع الإبلاغ (flag_type)، وسبب الإبلاغ (flag_reason).
تفاصيل حول عملية المراجعة، بما في ذلك المشرف الذي راجع الإبلاغ (reviewed_by_username)، وقرار المراجعة (review_status)، ووقت المراجعة (reviewed_at).
معلومات إضافية حول المنشور المُبلغ عنه، مثل ما إذا كان قد تم حذفه أو إخفاؤه، أو ما إذا كان قد تم كتم صوت المؤلف أو تعليقه.

flag_types:
تربط هذه CTE المعرفات الرقمية لأنواع إجراءات المنشورات بأسماء أنواع الإبلاغ التي يمكن قراءتها من قبل البشر:

3: خارج الموضوع
4: غير لائق
6: إخطار المستخدم
7: إخطار المشرفين
8: محتوى غير مرغوب فيه (Spam)
10: غير قانوني

median_time_to_act:
تحسب هذه CTE الوقت المتوسط (بالدقائق) المتخذ للرد على كل نوع من أنواع البلاغات. يتم حساب الوقت كفرق بين وقت إنشاء الإبلاغ (flagged_date) ووقت المراجعة (reviewed_at).

شرح النتائج

يجمع الاستعلام النهائي بيانات البلاغات المتفق عليها حسب نوع الإبلاغ ويوفر المقاييس التالية:

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

تم فرز النتائج حسب العدد الإجمالي للبلاغات بترتيب تنازلي.

أمثلة على النتائج

النوع	تم الإبلاغ	تلقائي	المجموع	الوقت المتوسط للرد (بالدقائق)	تم كتم صوت المستخدم	تم حذف المستخدم	تم حذف المنشور	تم إخفاء المنشور
محتوى غير مرغوب فيه	100	50	150	30	20	10	50	30
غير لائق	80	5	85	45	15	5	30	20
خارج الموضوع	40	2	42	25	5	0	10	15
إخطار_المشرفين	20	0	20	60	0	0	5	10
غير قانوني	5	1	6	120	1	1	3	2

إجراءات الإشراف المتخذة

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

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS user_flagged,
        COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS automation_flagged
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL -- Include mapped flag types
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- Include specific flag types for NULL results
),
warnings_issued AS (
    SELECT
        COUNT(*) AS warnings_count
    FROM
        user_warnings
    WHERE
        created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        COUNT(CASE 
            WHEN uh.action = 1 
                AND (
                    LOWER(uh.context) LIKE '%deleted via review queue%' OR
                    LOWER(uh.context) LIKE '%to be a spammer%' OR
                    LOWER(uh.context) LIKE '%review%' OR
                    LOWER(uh.context) LIKE '%reviewable user rejected%'
                ) 
            THEN 1 
        END) AS accounts_deleted,
        COUNT(CASE WHEN uh.action = 10 THEN 1 END) AS accounts_suspended
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
),
posts_deleted_and_hidden AS (
    SELECT
        COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS posts_deleted,
        COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS posts_hidden
    FROM
        flag_data fd
),
silences_issued AS (
    SELECT
        COUNT(*) AS silences_count
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    'Content flagged by users' AS category,
    fc.user_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Content flagged by automation' AS category,
    fc.automation_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Posts deleted for violating terms' AS category,
    pdh.posts_deleted AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Posts hidden' AS category,
    pdh.posts_hidden AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Warnings Issued' AS category,
    wi.warnings_count AS "Number of Cases"
FROM warnings_issued wi

UNION ALL

SELECT
    'Accounts deleted' AS category,
    vs.accounts_deleted AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Accounts suspended' AS category,
    vs.accounts_suspended AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Users silenced for 10+ years' AS category,
    si.silences_count AS "Number of Cases"
FROM silences_issued si

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية إجراءات الإشراف.
:end_date: تاريخ الانتهاء لتصفية إجراءات الإشراف.

شرح النتائج

يجمع الاستعلام إجراءات الإشراف في فئات ويوفر العدد الإجمالي للحالات لكل فئة. تتضمن الفئات:

المحتوى المُبلغ عنه من قبل المستخدمين: عدد المنشورات المُبلغ عنها من قبل المستخدمين العاديين (باستثناء البلاغات التي تم إنشاؤها بواسطة النظام).
المحتوى المُبلغ عنه بواسطة الأتمتة: عدد المنشورات المُبلغ عنها بواسطة الأنظمة أو الروبوتات الآلية (على سبيل المثال، spam_scanner_bot، system).
المنشورات المحذوفة بسبب انتهاك الشروط: عدد المنشورات التي تم حذفها بسبب انتهاك إرشادات المجتمع أو شروط الخدمة.
المنشورات المخفية: عدد المنشورات التي تم إخفاؤها (ولكن لم يتم حذفها) لأسباب مختلفة.
التحذيرات الصادرة: عدد التحذيرات الصادرة للمستخدمين بسبب السلوك أو المحتوى غير اللائق.
الحسابات المحذوفة: عدد حسابات المستخدمين المحذوفة بسبب الانتهاكات، مثل تم تصنيفها كمحتوى غير مرغوب فيه أو رفضها في طوابير المراجعة.
الحسابات المعلقة: عدد حسابات المستخدمين المعلقة لفترة محددة بسبب الانتهاكات.
المستخدمين المكتمين لمدة 10 سنوات أو أكثر: عدد المستخدمين المكتمين بشكل دائم أو لفترات طويلة (10 سنوات أو أكثر).

أمثلة على النتائج

الفئة	عدد الحالات
المحتوى المُبلغ عنه من قبل المستخدمين	150
المحتوى المُبلغ عنه بواسطة الأتمتة	100
المنشورات المحذوفة بسبب انتهاك الشروط	50
المنشورات المخفية	30
التحذيرات الصادرة	20
الحسابات المحذوفة	10
الحسابات المعلقة	15
المستخدمين المكتمين لمدة 10 سنوات أو أكثر	5

إجراءات الإشراف الفردية المتخذة

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

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by users' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system')
    
    UNION ALL

    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by automation' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username IN ('spam_scanner_bot', 'system')
),
posts_deleted_and_hidden AS (
    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_deleted_at AS action_date,
        'Posts deleted for violating terms' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_deleted_at IS NOT NULL
    
    UNION ALL

    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_hidden_at AS action_date,
        'Posts hidden' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_hidden_at IS NOT NULL
),
warnings_issued AS (
    SELECT
        CAST(uw.created_by_id AS TEXT) AS acting_user,
        CAST(uw.user_id AS TEXT) AS target_user,
        uw.created_at AS action_date,
        'Warnings Issued' AS category,
        NULL AS context
    FROM
        user_warnings uw
    WHERE
        uw.created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts deleted' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 1
        AND (
            LOWER(uh.context) LIKE '%deleted via review queue%' OR
            LOWER(uh.context) LIKE '%to be a spammer%' OR
            LOWER(uh.context) LIKE '%review%' OR
            LOWER(uh.context) LIKE '%reviewable user rejected%'
        )
    
    UNION ALL

    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts suspended' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 10
),
silences_issued AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Users silenced for 10+ years' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM flagged_content

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM posts_deleted_and_hidden

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM warnings_issued

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM violations_and_suspensions

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM silences_issued

المعلمات المستخدمة

:start_date: تاريخ البدء لتصفية إجراءات الإشراف الفردية.
:end_date: تاريخ الانتهاء لتصفية إجراءات الإشراف الفردية.

شرح النتائج

يوفر الاستعلام سجلاً مفصلاً لإجراءات الإشراف الفردية، بما في ذلك:

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

المحتوى المُبلغ عنه من قبل المستخدمين
المحتوى المُبلغ عنه بواسطة الأتمتة
المنشورات المحذوفة بسبب انتهاك الشروط
المنشورات المخفية
التحذيرات الصادرة
الحسابات المحذوفة
الحسابات المعلقة
المستخدمين المكتمين لمدة 10 سنوات أو أكثر

السياق: معلومات إضافية أو محتوى متعلق بالإجراء، مثل نص منشور مُبلغ عنه أو سبب التعليق.

أمثلة على النتائج

المستخدم الفاعل	المستخدم المستهدف	تاريخ الإجراء	الفئة	السياق
user123	user456	2024-02-01 10:00	المحتوى المُبلغ عنه من قبل المستخدمين	“يحتوي هذا المنشور على محتوى غير مرغوب فيه.”
spam_scanner	user789	2024-02-02 12:00	المحتوى المُبلغ عنه بواسطة الأتمتة	“تم اكتشافه كمحتوى غير مرغوب فيه بواسطة النظام.”
mod001	user456	2024-02-03 14:00	المنشورات المحذوفة بسبب انتهاك الشروط	“محتوى منشور مثال
mod002	user123	2024-02-04 16:00	المنشورات المخفية	“اعتبر المنشور غير لائق.”
admin001	user789	2024-02-05 18:00	التحذيرات الصادرة	NULL
admin002	user456	2024-02-06 20:00	الحسابات المحذوفة	“تم حذف الحساب عبر طابور المراجعة.”
mod003	user123	2024-02-07 22:00	الحسابات المعلقة	“تم تعليق المستخدم بسبب المحتوى غير المرغوب فيه المتكرر.”
admin003	user789	2024-02-08 08:00	المستخدمين المكتمين لمدة 10 سنوات أو أكثر	“تم كتم صوت المستخدم بسبب الانتهاكات الشديدة.”

الموضوع		الردود	مرات العرض
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	439	22 ديسمبر 2023
Dashboard Report - User Flagging Ratio Data & reporting sql-query , dashboard-reports , dashboard-sql	0	372	6 فبراير 2024
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	844	30 أكتوبر 2024
Statistics on spam flags reported by AI triage automation Data & reporting ai , sql-query , spam	3	173	31 مايو 2025
Show what action was taken and by who for Flags Feature	2	926	12 أبريل 2014

تحليل تقارير أنشطة المراجعة والإبلاغ عن المخالفات

نسبة حل بلاغات المنشورات حسب النوع

شرح استعلام SQL

المعلمات المستخدمة

شرح الجداول المؤقتة (CTEs)

شرح النتائج

نتائج مثال

نسب حل العناصر القابلة للمراجعة

شرح استعلام SQL

المعلمات المستخدمة

شرح الجداول المؤقتة (CTEs)

شرح النتائج

نتائج مثال

حلول بلاغات المشرفين

شرح استعلام SQL

المعلمات المستخدمة

شرح الجداول المؤقتة (CTEs)

شرح النتائج

نتائج مثال

من يقوم بالإبلاغ عن المنشورات

شرح استعلام SQL

المعلمات المستخدمة

شرح النتائج

نتائج مثال

ملاحظات المستخدمين

شرح استعلام SQL

المعلمات المستخدمة

شرح النتائج

نتائج مثال

مؤشرات أداء رئيسية للمشرفين - البلاغات ومتوسط وقت حل البلاغ

شرح استعلام SQL

المعلمات المستخدمة

شرح الجداول المؤقتة (CTEs)

شرح النتائج

نتائج مثال

جميع بيانات البلاغات

شرح استعلام SQL

المعلمات المستخدمة

شرح الجداول المؤقتة (CTEs)

شرح النتائج

نتائج مثال (مجهولة)

نتائج مثال (مجهولة)

تقارير البلاغات المقدمة

المعلمات المستخدمة

شرح CTEs

شرح النتائج

أمثلة على النتائج

الحظر والعلّق:

المعلمات المستخدمة

شرح النتائج

أمثلة على النتائج

إجراءات الإبلاغ المتفق عليها

المعلمات المستخدمة

شرح CTEs

شرح النتائج

أمثلة على النتائج

إجراءات الإشراف المتخذة

المعلمات المستخدمة

شرح النتائج

أمثلة على النتائج

إجراءات الإشراف الفردية المتخذة

المعلمات المستخدمة

شرح النتائج

أمثلة على النتائج

الموضوعات ذات الصلة