Analyse von Moderations- und Meldeaktivitätsberichten

SaraDev · 13. Februar 2025 um 23:08

Die Aufrechterhaltung einer gesunden und inklusiven Community erfordert eine effektive Moderation, die unter anderem die Überprüfung von gemeldeten Beiträgen, die Analyse der Moderatorenleistung und die Verwaltung von Benutzerinhalten umfassen kann.

Dieser Leitfaden enthält eine Vielzahl von SQL-Berichten für Discourse, die zur Analyse von moderationsbezogenen Aktivitäten beitragen sollen.

In diesem Thema findest du detaillierte Data Explorer-Abfragen für:

Statistiken zur Auflösung von Meldungen (Flags).
Prozentsätze der Auflösung überprüfbarer Elemente (Reviewables).
Spezifische Leistungskennzahlen für Moderatoren.
Einblicke in die Meldeaktivität von Benutzern.
Umfassende Daten aller gemeldeten Benutzer-, Beitrags- und Themenaktionen.

Prozentsatz der Auflösung von Beitragsmeldungen nach Typ

Erklärung der SQL-Abfrage

Diese Abfrage berechnet den Prozentsatz der Auflösungen (zustimmend, ablehnend, zurückgestellt, gelöscht) für gemeldete Beiträge, gruppiert nach Melde-Typ.

-- [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 -- Zeit bis zur Auflösung in Minuten
    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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern der gemeldeten Beiträge.
:end_date: Das Enddatum zum Filtern der gemeldeten Beiträge.

Erklärung der CTEs (Common Table Expressions)

period_actions: Filtert gemeldete Beiträge innerhalb des angegebenen Datumsbereichs und berechnet die Zeit bis zur Auflösung für jede Meldung.
flag_types: Ordnet den IDs der Melde-Typen menschenlesbare Namen zu (z. B. Off-Topic, Unangemessen, Spam).
flag_resolutions: Gruppiert Meldungen nach Typ und Auflösung (zustimmend, ablehnend, zurückgestellt, gelöscht) und zählt die Vorkommen jeder Auflösung.
flag_totals: Berechnet die Gesamtzahl der Meldungen für jeden Melde-Typ.
resolution_percentages: Kombiniert die Auflösungsanzahlen und die Gesamtzahl der Meldungen, um den Prozentsatz jedes Auflösungstyps für jeden Melde-Typ zu berechnen.
pivoted_data: Pivoted die Daten, um die Prozentsätze und Anzahlen der Auflösungen in separaten Spalten für jeden Auflösungstyp anzuzeigen.

Erklärung der Ergebnisse

Das Endergebnis ist eine Tabelle, die Folgendes zeigt:

Melde-Typ (z. B. Off-Topic, Spam).
Prozentsätze und Anzahlen für jeden Auflösungstyp (zustimmend, ablehnend, zurückgestellt, gelöscht).
Gesamtzahl der Meldungen für jeden Melde-Typ.

Beispielhafte Ergebnisse

Melde-Typ	Zustimmend %	Zustimmend Anzahl	Ablehnend %	Ablehnend Anzahl	Zurückgestellt %	Zurückgestellt Anzahl	Gelöscht %	Gelöscht Anzahl	Gesamtzahl Meldungen
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

Auflösungsprozentsätze für überprüfbare Elemente (Reviewables)

Erklärung der SQL-Abfrage

Diese Abfrage analysiert die Auflösungsstatus von überprüfbaren Elementen (z. B. gemeldeten Beiträgen) innerhalb eines angegebenen Datumsbereichs. Sie berechnet den Prozentsatz und die Anzahl jedes Auflösungsstatus für jeden Melde-Typ.

-- [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,
    -- Prozentsätze
    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,
    -- Anzahlen
    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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern der überprüfbaren Elemente.
:end_date: Das Enddatum zum Filtern der überprüfbaren Elemente.

Erklärung der CTEs (Common Table Expressions)

flag_data: Gruppiert überprüfbare Elemente nach Melde-Typ und Auflösungsstatus und zählt die Vorkommen jeder Kombination.
flag_totals: Berechnet die Gesamtzahl der Meldungen für jeden Melde-Typ.
flag_percentages: Kombiniert die Melde-Anzahlen und die Gesamtzahlen, um den Prozentsatz jedes Auflösungsstatus für jeden Melde-Typ zu berechnen.

Erklärung der Ergebnisse

Das Endergebnis ist eine Tabelle, die Folgendes zeigt:

Melde-Typ.
Prozentsätze und Anzahlen für jeden Auflösungsstatus (ausstehend, genehmigt, abgelehnt, ignoriert, gelöscht).

Beispielhafte Ergebnisse

Melde-Typ	Ausstehend %	Ausstehend Anzahl	Genehmigt %	Genehmigt Anzahl	Abgelehnt %	Abgelehnt Anzahl	Ignoriert %	Ignoriert Anzahl	Gelöscht %	Gelöscht Anzahl
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

Auflösungen von Meldungen durch Moderatoren

Erklärung der SQL-Abfrage

Diese Abfrage bietet Einblicke in die Aktivitäten der Moderatoren, indem sie zeigt, welche Moderatoren gemeldete Beiträge aufgelöst haben, welche Melde-Typen sie bearbeitet haben und welche Auflösungen sie angewendet haben. Sie berechnet Prozentsätze und Anzahlen für jeden Auflösungstyp.

-- [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 -- Zeit bis zur Auflösung in Minuten
    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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern der gemeldeten Beiträge.
:end_date: Das Enddatum zum Filtern der gemeldeten Beiträge.
:only_staff: Ein boolescher Parameter, um die Ergebnisse auf Mitarbeiter (Staff) zu beschränken.

Erklärung der CTEs (Common Table Expressions)

period_actions: Filtert gemeldete Beiträge innerhalb des angegebenen Datumsbereichs und berechnet die Zeit bis zur Auflösung für jede Meldung.
flag_types: Ordnet den IDs der Melde-Typen menschenlesbare Namen zu.
flag_resolutions: Gruppiert Meldungen nach Benutzer, Melde-Typ und Auflösung und zählt die Vorkommen jeder Kombination.
flag_totals: Berechnet die Gesamtzahl der Meldungen für jeden Benutzer und Melde-Typ.
resolution_percentages: Kombiniert die Auflösungsanzahlen und die Gesamtzahlen, um Prozentsätze für jeden Auflösungstyp zu berechnen.
pivoted_data: Pivoted die Daten, um die Prozentsätze und Anzahlen der Auflösungen in separaten Spalten für jeden Auflösungstyp anzuzeigen.

Erklärung der Ergebnisse

Das Endergebnis ist eine Tabelle, die Folgendes zeigt:

Benutzername des Moderators.
Prozentsätze und Anzahlen für jeden Auflösungstyp (zustimmend, ablehnend, zurückgestellt, gelöscht).
Gesamtzahl der von jedem Moderator bearbeiteten Meldungen.

Beispielhafte Ergebnisse

Moderator	Melde-Typ	Zustimmend %	Zustimmend Anzahl	Ablehnend %	Ablehnend Anzahl	Zurückgestellt %	Zurückgestellt Anzahl	Gelöscht %	Gelöscht Anzahl	Gesamtzahl Meldungen
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

Wer meldet Beiträge

Erklärung der SQL-Abfrage

Diese Abfrage identifiziert die Benutzer, die Beiträge innerhalb eines angegebenen Datumsbereichs gemeldet haben, und berechnet die Gesamtzahl der von jedem Benutzer eingereichten Meldungen.

-- [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) -- Melde-Typen
  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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern der gemeldeten Beiträge.
:end_date: Das Enddatum zum Filtern der gemeldeten Beiträge.
:only_staff: Ein boolescher Parameter, um die Ergebnisse auf Mitarbeiter (Staff) zu beschränken.

Erklärung der Ergebnisse

Das Endergebnis ist eine rangierte Liste von Benutzern mit ihren Melde-Anzahlen.

Beispielhafte Ergebnisse

Benutzer-ID	Benutzername	Melde-Anzahl
1	user1	50
2	user2	30
3	user3	20

Benutzer-Notizen

Erklärung der SQL-Abfrage

Diese Abfrage ruft Benutzer-Notizen aus der Tabelle plugin_store_rows ab. Sie extrahiert Details wie die Benutzer-ID, das Erstellungsdatum, den Notizinhalt und die ID des Erstellers.

-- [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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern der Benutzer-Notizen.
:end_date: Das Enddatum zum Filtern der Benutzer-Notizen.

Erklärung der Ergebnisse

Das Endergebnis ist eine detaillierte Liste von Benutzer-Notizen mit relevanten Metadaten.

Beispielhafte Ergebnisse

Benutzer-ID	Erstellt am	Benutzer-Notiz	Erstellt von Benutzer-ID
1	2025-01-01	Dieser Benutzer ist hilfsbereit.	2
2	2025-02-01	Dieser Benutzer ist mit zwei anderen Benutzerkonten verknüpft.	3

KPIs für Moderatoren – Meldungen und durchschnittliche Auflösungszeit

Erklärung der SQL-Abfrage

Diese Abfrage bewertet die Leistung der Moderatoren, indem sie die Anzahl der bearbeiteten Meldungen und die durchschnittliche Auflösungszeit (in Minuten) für jeden Moderator berechnet.

-- [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 -- Zeit bis zur Auflösung in Minuten
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- Melde-Typen
      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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern der gemeldeten Beiträge.
:end_date: Das Enddatum zum Filtern der gemeldeten Beiträge.

Erklärung der CTEs (Common Table Expressions)

period_actions: Filtert gemeldete Beiträge innerhalb des angegebenen Datumsbereichs und berechnet die Zeit bis zur Auflösung für jede Meldung.
moderator_actions: Identifiziert Meldungen, die von Moderatoren aufgelöst wurden, und berechnet die Zeit bis zur Auflösung für jede Meldung.
moderator_stats: Gruppiert Meldungen nach Moderator und berechnet die Gesamtzahl der bearbeiteten Meldungen sowie die durchschnittliche Auflösungszeit.

Erklärung der Ergebnisse

Das Endergebnis ist eine rangierte Liste von Moderatoren mit ihren Anzahlen bearbeiteter Meldungen und durchschnittlichen Auflösungszeiten.

Beispielhafte Ergebnisse

Benutzername des Moderators	Bearbeitete Meldungen	Durchschnittliche Auflösungszeit (Minuten)
mod1	50	15.00
mod2	30	20.00

Alle Melde-Daten

Erklärung der SQL-Abfrage

Diese Abfrage bietet einen umfassenden Datensatz aller gemeldeten Benutzer-, Beitrags- und Thementaten innerhalb eines angegebenen Datumsbereichs. Sie kombiniert Daten aus mehreren Tabellen, um Details wie den Melde-Typ, das gemeldete Element, den Meldegrund, die Meldequelle, die Auflösungsentscheidung und zugehörige Nachrichten einzubeziehen.

-- [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 -- Nur Meldungen einschließen, denen zugestimmt wurde und bei denen Maßnahmen ergriffen wurden
),
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, -- Entfernt nur URLs
    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 -- Zeitdifferenz in Minuten
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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern der gemeldeten Beiträge.
:end_date: Das Enddatum zum Filtern der gemeldeten Beiträge.

Erklärung der CTEs (Common Table Expressions)

flag_data: Ruft detaillierte Informationen über gemeldete Beiträge ab, einschließlich der Melde-ID, des gemeldeten Elements, des Melde-Typs, des Meldegrunds, der Meldequelle und der Überprüfungsdetails.
review_decisions: Ordnet den Überprüfungs-Status-Codes menschenlesbare Entscheidungs-Namen zu (z. B. ausstehend, zustimmend, ablehnend, ignoriert).
flag_types: Ordnet den IDs der Beitragsaktionstypen menschenlesbare Namen der Melde-Typen zu (z. B. Off-Topic, Unangemessen, Spam).

Erklärung der Ergebnisse

Melde-ID: Die eindeutige Kennung für die Meldung.
Gemeldetes Element: Die ID des gemeldeten Beitrags.
Gemeldet von Benutzername: Der Benutzername des Benutzers, der den Beitrag gemeldet hat.
Melde-Datum: Das Datum, an dem die Meldung erstellt wurde.
Melde-Typ: Der numerische Typ der Meldung.
Name des Melde-Typs: Der menschenlesbare Name des Melde-Typs (z. B. Off-Topic, Spam).
Überprüfbar durch Moderator: Zeigt an, ob die Meldung von einem Benutzer oder dem System erstellt wurde.
Melde-Grund: Der angegebene Grund für die Meldung.
Text des gemeldeten Elements: Der Inhalt des gemeldeten Beitrags.
ID der zugehörigen Nachricht: Die ID einer zugehörigen Nachricht (falls zutreffend).
Text der zugehörigen Nachricht: Der Inhalt der zugehörigen Nachricht, wobei URLs zur Übersichtlichkeit entfernt wurden.
Überprüft von Benutzername: Der Benutzername des Moderators, der die Meldung überprüft hat.
Überprüfungs-Entscheidung: Die Entscheidung des Überprüfers (z. B. zustimmend, ablehnend, ignoriert, gelöscht).
Ergreifene Maßnahme: Die als Ergebnis der Meldung ergriffene Maßnahme, wie z. B. das Stummschalten oder Sperren des Benutzers, das Löschen oder Verbergen des Beitrags oder keine Maßnahme.
Überprüfungszeit (Minuten): Die Zeit, die zur Überprüfung der Meldung benötigt wurde, berechnet als Differenz zwischen der Erstellung der Meldung und der Überprüfungszeit, in Minuten.

Beispielhafte Ergebnisse (Anonymisiert)

Melde-ID	Gemeldetes Element	Gemeldet von Benutzername	Melde-Datum	Melde-Typ	Name des Melde-Typs	Überprüfbar durch Moderator	Melde-Grund	Text des gemeldeten Elements	ID der zugehörigen Nachricht	Text der zugehörigen Nachricht	Überprüft von Benutzername	Überprüfungs-Entscheidung	Ergreifene Maßnahme	Überprüfungszeit (Minuten)
12345	67890	user123	2025-04-01 12:00	8	Spam	true	Spam-Inhalt	„Jetzt kaufen auf spam.com“	98765	„Schau dir das an!“	mod456	Zustimmend	Beitrag gelöscht	15.25
12346	67891	user124	2025-04-02 14:30	4	Unangemessen	false	Beleidigend	„Das ist unangemessen!“	NULL	NULL	mod457	Ablehnend	Keine Maßnahme ergriffen	30.50

Eingereichte Melde-Berichte

Dieser Bericht bietet einen Überblick über die innerhalb des angegebenen Datumsbereichs eingereichten Meldungen. Er kategorisiert Meldungen nach ihrem Typ (z. B. Spam, Unangemessen) und unterscheidet zwischen von Benutzern gemeldeten und vom System generierten Meldungen. Der Bericht enthält die Gesamtzahl der Meldungen für jeden Typ, was hilft, die häufigsten Probleme auf der Plattform zu identifizieren.

-- [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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern gemeldeter Beiträge.
:end_date: Das Enddatum zum Filtern gemeldeter Beiträge.

Erklärung der CTEs

flag_data:
Diese CTE ruft detaillierte Informationen über gemeldete Beiträge ab, einschließlich:

Der eindeutigen ID der Meldung (flag_id), der ID des gemeldeten Beitrags (post_id) und des Themas, dem er angehört (topic_id).
Informationen zur Meldung selbst, wie der Benutzer, der die Meldung erstellt hat (flagged_by_username), das Datum der Meldung (flagged_date), der Meldungsart (flag_type) und der Grund für die Meldung (flag_reason).
Details zum Review-Prozess, einschließlich des Moderators, der die Meldung überprüft hat (reviewed_by_username), der Review-Entscheidung (review_status) und der Uhrzeit der Überprüfung (reviewed_at).

flag_types:
Diese CTE ordnet numerische IDs für Beitragshandlungstypen menschenlesbaren Namen für Meldungsarten zu:

3: Off-Topic
4: Unangemessen
6: Benutzer benachrichtigen
7: Moderatoren benachrichtigen
8: Spam
10: Illegal
NULL: Sonstiges

Erklärung der Ergebnisse

Die finale Abfrage aggregiert die Meldungsdaten nach Meldungsart und liefert die folgenden Metriken:

Typ: Der menschenlesbare Name der Meldungsart (z. B. Off-Topic, Spam).
Gemeldet: Die Anzahl der von Benutzern eingereichten Meldungen (ohne systemgenerierte Meldungen).
Automatisch: Die Anzahl der vom System oder Bots generierten Meldungen (z. B. spam_scanner_bot, system).
Gesamt: Die Gesamtzahl der Meldungen für jede Art.

Die Ergebnisse sind nach der Gesamtzahl der Meldungen in absteigender Reihenfolge sortiert.

Beispielhafte Ergebnisse

Typ	Gemeldet	Automatisch	Gesamt
Spam	120	80	200
Unangemessen	90	10	100
Off-Topic	60	5	65
Moderatoren_benachrichtigen	30	0	30
Illegal	10	2	12
Sonstiges	5	0	5

Sperren und Stummschaltungen:

Dieser Bericht listet Benutzer auf, die im angegebenen Datumsbereich gesperrt oder stummgeschaltet wurden. Er enthält Details wie die Sperren- oder Stummschaltungsdaten, die Dauer der Maßnahme sowie die Erstellungs- und letzte Aktivitätsdaten des Benutzerkontos. Dieser Bericht ist nützlich, um Moderationsmaßnahmen zu überwachen und Muster im Benutzerverhalten zu identifizieren, die zu Sperren oder Stummschaltungen führen.

-- [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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern gesperrter oder stummgeschalteter Benutzer.
:end_date: Das Enddatum zum Filtern gesperrter oder stummgeschalteter Benutzer.

Erklärung der Ergebnisse

Diese Abfrage ruft eine Liste von Benutzern ab, die im angegebenen Datumsbereich gesperrt oder stummgeschaltet wurden. Wichtige Spalten sind:

Benutzer-ID: Der eindeutige Bezeichner für den Benutzer.
Benutzername: Der Benutzername des Benutzers.
Name: Der vollständige Name des Benutzers (falls verfügbar).
Gesperrt am: Das Datum, an dem der Benutzer gesperrt wurde.
Gesperrt bis: Das Datum, bis zu dem der Benutzer gesperrt ist.
Stummgeschaltet bis: Das Datum, bis zu dem der Benutzer stummgeschaltet ist.
Konto erstellt am: Das Datum, an dem das Benutzerkonto erstellt wurde.
Zuletzt gesehen am: Der letzte Zeitpunkt, zu dem der Benutzer auf der Plattform aktiv war.
Meldungsstufe: Die aktuelle Meldungsstufe des Benutzers.
Admin: Ob der Benutzer ein Administrator ist (true/false).
Moderator: Ob der Benutzer ein Moderator ist (true/false).

Die Ergebnisse sind nach dem Sperrdatum (suspended_at) und dem Stummschaltungsdatum (silenced_till) in absteigender Reihenfolge sortiert, wobei Nullwerte zuletzt erscheinen.

Beispielhafte Ergebnisse

Benutzer-ID	Benutzername	Name	Gesperrt am	Gesperrt bis	Stummgeschaltet bis	Konto erstellt am	Zuletzt gesehen am	Meldungsstufe	Admin	Moderator
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

Durchgeführte Maßnahmen bei akzeptierten Meldungen

Dieser Bericht konzentriert sich auf Meldungen, die von Moderatoren akzeptiert wurden und zu Maßnahmen geführt haben. Er kategorisiert Meldungen nach Art und liefert Metriken wie die Gesamtzahl der Meldungen, die Medianzeit für die Durchführung der Maßnahmen und die Ergebnisse (z. B. stummgeschaltete Benutzer, gelöschte Beiträge).

-- [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 -- Nur Meldungen einschließen, die akzeptiert wurden und Maßnahmen ergriffen wurden
),
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 -- Gemappte Meldungsarten einschließen
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- Bestimmte Meldungsarten für NULL-Ergebnisse einschließen
        )
    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 "Medianzeit für Maßnahme (Minuten)",
    COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "Benutzer stummgeschaltet",
    COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "Benutzer gelöscht",
    COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Beitrag gelöscht",
    COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Beitrag ausgeblendet"

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 -- Gemappte Meldungsarten einschließen
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- Bestimmte Meldungsarten für NULL-Ergebnisse einschließen
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

Verwendete Parameter

:start_date: Das Startdatum zum Filtern akzeptierter Meldungen.
:end_date: Das Enddatum zum Filtern akzeptierter Meldungen.

Erklärung der CTEs

flag_data:
Diese CTE ruft detaillierte Informationen über Meldungen ab, die akzeptiert wurden und zu Maßnahmen geführt haben. Sie enthält:

Die eindeutige ID der Meldung (flag_id), die ID des gemeldeten Beitrags (post_id) und das Thema, dem er angehört (topic_id).
Informationen zur Meldung selbst, wie der Benutzer, der die Meldung erstellt hat (flagged_by_username), das Datum der Meldung (flagged_date), die Art der Meldung (flag_type) und der Grund für die Meldung (flag_reason).
Details zum Review-Prozess, einschließlich des Moderators, der die Meldung überprüft hat (reviewed_by_username), der Review-Entscheidung (review_status) und der Uhrzeit der Überprüfung (reviewed_at).
Zusätzliche Informationen zum gemeldeten Beitrag, wie ob er gelöscht oder ausgeblendet wurde, oder ob der Autor stummgeschaltet oder gesperrt wurde.

flag_types:
Diese CTE ordnet numerische IDs für Beitragshandlungstypen menschenlesbaren Namen für Meldungsarten zu:

3: Off-Topic
4: Unangemessen
6: Benutzer benachrichtigen
7: Moderatoren benachrichtigen
8: Spam
10: Illegal

median_time_to_act:
Diese CTE berechnet die Medianzeit (in Minuten), die für die Durchführung von Maßnahmen für jede Meldungsart benötigt wurde. Die Zeit wird als Differenz zwischen dem Erstellungszeitpunkt der Meldung (flagged_date) und dem Überprüfungszeitpunkt (reviewed_at) berechnet.

Erklärung der Ergebnisse

Die finale Abfrage aggregiert die Daten der akzeptierten Meldungen nach Meldungsart und liefert die folgenden Metriken:

Typ: Der menschenlesbare Name der Meldungsart (z. B. Off-Topic, Spam).
Gemeldet: Die Anzahl der von Benutzern eingereichten Meldungen (ohne systemgenerierte Meldungen).
Automatisch: Die Anzahl der vom System oder Bots generierten Meldungen (z. B. spam_scanner_bot, system).
Gesamt: Die Gesamtzahl der Meldungen für jede Art.
Medianzeit für Maßnahme (Minuten): Die Medianzeit, die für die Durchführung von Maßnahmen für Meldungen dieser Art in Minuten benötigt wurde.
Benutzer stummgeschaltet: Die Anzahl der Meldungen, die dazu führten, dass der Benutzer stummgeschaltet wurde.
Benutzer gelöscht: Die Anzahl der Meldungen, die dazu führten, dass der Benutzer gesperrt wurde.
Beitrag gelöscht: Die Anzahl der Meldungen, die dazu führten, dass der Beitrag gelöscht wurde.
Beitrag ausgeblendet: Die Anzahl der Meldungen, die dazu führten, dass der Beitrag ausgeblendet wurde.

Die Ergebnisse sind nach der Gesamtzahl der Meldungen in absteigender Reihenfolge sortiert.

Beispielhafte Ergebnisse

Typ	Gemeldet	Automatisch	Gesamt	Medianzeit für Maßnahme (Minuten)	Benutzer stummgeschaltet	Benutzer gelöscht	Beitrag gelöscht	Beitrag ausgeblendet
Spam	100	50	150	30	20	10	50	30
Unangemessen	80	5	85	45	15	5	30	20
Off-Topic	40	2	42	25	5	0	10	15
Moderatoren_benachrichtigen	20	0	20	60	0	0	5	10
Illegal	5	1	6	120	1	1	3	2

Durchgeführte Moderationsmaßnahmen

Dieser Bericht bietet eine Zusammenfassung der im angegebenen Datumsbereich durchgeführten Moderationsmaßnahmen. Er aggregiert verschiedene Arten akzeptierter Maßnahmen, einschließlich von Benutzern oder Automatisierung gemeldeter Inhalte, gelöschter oder ausgeblendeter Beiträge, erteilter Warnungen, gelöschter oder gesperrter Konten und für längere Zeit stummgeschalteter Benutzer. Jede Kategorie wird mit der Gesamtzahl der Fälle dargestellt, was einen Überblick über die Moderationsaktivität bietet.

-- [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 -- Nur Meldungen einschließen, die akzeptiert wurden und Maßnahmen ergriffen wurden
),
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 -- Gemappte Meldungsarten einschließen
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- Bestimmte Meldungsarten für NULL-Ergebnisse einschließen
),
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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern von Moderationsmaßnahmen.
:end_date: Das Enddatum zum Filtern von Moderationsmaßnahmen.

Erklärung der Ergebnisse

Die Abfrage aggregiert Moderationsmaßnahmen in Kategorien und liefert die Gesamtzahl der Fälle für jede Kategorie. Die Kategorien umfassen:

Von Benutzern gemeldete Inhalte: Die Anzahl der von regulären Benutzern gemeldeten Beiträge (ohne systemgenerierte Meldungen).
Von Automatisierung gemeldete Inhalte: Die Anzahl der von automatisierten Systemen oder Bots gemeldeten Beiträge (z. B. spam_scanner_bot, system).
Beiträge wegen Verstößen gelöscht: Die Anzahl der Beiträge, die aufgrund von Verstößen gegen Community-Richtlinien oder Nutzungsbedingungen gelöscht wurden.
Beiträge ausgeblendet: Die Anzahl der Beiträge, die aus verschiedenen Gründen ausgeblendet (aber nicht gelöscht) wurden.
Warnungen erteilt: Die Anzahl der an Benutzer für unangemessenes Verhalten oder Inhalte erteilten Warnungen.
Konten gelöscht: Die Anzahl der Benutzerkonten, die aufgrund von Verstößen gelöscht wurden, z. B. weil sie als Spammer gemeldet oder in Review-Warteschlangen abgelehnt wurden.
Konten gesperrt: Die Anzahl der Benutzerkonten, die für einen bestimmten Zeitraum aufgrund von Verstößen gesperrt wurden.
Benutzer für 10+ Jahre stummgeschaltet: Die Anzahl der Benutzer, die dauerhaft oder für längere Zeiträume (10+ Jahre) stummgeschaltet wurden.

Beispielhafte Ergebnisse

Kategorie	Anzahl der Fälle
Von Benutzern gemeldete Inhalte	150
Von Automatisierung gemeldete Inhalte	100
Beiträge wegen Verstößen gelöscht	50
Beiträge ausgeblendet	30
Warnungen erteilt	20
Konten gelöscht	10
Konten gesperrt	15
Benutzer für 10+ Jahre stummgeschaltet	5

Individuelle durchgeführte Moderationsmaßnahmen

Dieser Bericht bietet ein detailliertes Protokoll der im angegebenen Datumsbereich durchgeführten individuellen Moderationsmaßnahmen. Er enthält Informationen über den Benutzer, der die Maßnahme durchgeführt hat, den Zielbenutzer, das Datum der Maßnahme, die Kategorie der Maßnahme (z. B. gemeldete Inhalte, gelöschte Beiträge, erteilte Warnungen) und den Kontext oder Grund für die Maßnahme. Dieser Bericht ist nützlich, um spezifische Moderationsentscheidungen zu prüfen und den Kontext hinter jeder Maßnahme zu verstehen.

-- [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 -- Nur Meldungen einschließen, die akzeptiert wurden und Maßnahmen ergriffen wurden
),
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

Verwendete Parameter

:start_date: Das Startdatum zum Filtern individueller Moderationsmaßnahmen.
:end_date: Das Enddatum zum Filtern individueller Moderationsmaßnahmen.

Erklärung der Ergebnisse

Die Abfrage bietet ein detailliertes Protokoll individueller Moderationsmaßnahmen, einschließlich:

Handelnder Benutzer: Der Benutzername des Moderators, des Systems oder des Benutzers, der die Maßnahme durchgeführt hat.
Zielbenutzer: Der Benutzername oder die ID des Benutzers, der Gegenstand der Maßnahme war (z. B. der Autor eines gemeldeten Beitrags oder der Empfänger einer Warnung).
Datum der Maßnahme: Das Datum und die Uhrzeit, zu denen die Maßnahme durchgeführt wurde.
Kategorie: Die Art der Moderationsmaßnahme, wie z. B.:

Von Benutzern gemeldete Inhalte
Von Automatisierung gemeldete Inhalte
Beiträge wegen Verstößen gelöscht
Beiträge ausgeblendet
Warnungen erteilt
Konten gelöscht
Konten gesperrt
Benutzer für 10+ Jahre stummgeschaltet

Kontext: Zusätzliche Informationen oder Inhalte im Zusammenhang mit der Maßnahme, wie der Text eines gemeldeten Beitrags oder der Grund für eine Sperrung.

Beispielhafte Ergebnisse

Handelnder Benutzer	Zielbenutzer	Datum der Maßnahme	Kategorie	Kontext
user123	user456	2024-02-01 10:00	Von Benutzern gemeldete Inhalte	„Dieser Beitrag enthält Spam-Inhalte.“
spam_scanner	user789	2024-02-02 12:00	Von Automatisierung gemeldete Inhalte	„Als Spam vom System erkannt.“
mod001	user456	2024-02-03 14:00	Beiträge wegen Verstößen gelöscht	„Beispielhafter Beitragsinhalt
mod002	user123	2024-02-04 16:00	Beiträge ausgeblendet	„Beitrag als unangemessen eingestuft.“
admin001	user789	2024-02-05 18:00	Warnungen erteilt	NULL
admin002	user456	2024-02-06 20:00	Konten gelöscht	„Konto über Review-Warteschlange gelöscht.“
mod003	user123	2024-02-07 22:00	Konten gesperrt	„Benutzer wegen wiederholten Spams gesperrt.“
admin003	user789	2024-02-08 08:00	Benutzer für 10+ Jahre stummgeschaltet	„Benutzer wegen extremer Verstöße stummgeschaltet.“

Thema		Antworten	Aufrufe
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	434	22. Dezember 2023
Dashboard Report - User Flagging Ratio Data & reporting sql-query , dashboard-reports , dashboard-sql	0	358	6. Februar 2024
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	804	30. Oktober 2024
Statistics on spam flags reported by AI triage automation Data & reporting ai , sql-query , spam	3	151	31. Mai 2025
Show what action was taken and by who for Flags Feature	2	916	12. April 2014

Analyse von Moderations- und Meldeaktivitätsberichten

Prozentsatz der Auflösung von Beitragsmeldungen nach Typ

Erklärung der SQL-Abfrage

Verwendete Parameter

Erklärung der CTEs (Common Table Expressions)

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Auflösungsprozentsätze für überprüfbare Elemente (Reviewables)

Erklärung der SQL-Abfrage

Verwendete Parameter

Erklärung der CTEs (Common Table Expressions)

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Auflösungen von Meldungen durch Moderatoren

Erklärung der SQL-Abfrage

Verwendete Parameter

Erklärung der CTEs (Common Table Expressions)

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Wer meldet Beiträge

Erklärung der SQL-Abfrage

Verwendete Parameter

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Benutzer-Notizen

Erklärung der SQL-Abfrage

Verwendete Parameter

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

KPIs für Moderatoren – Meldungen und durchschnittliche Auflösungszeit

Erklärung der SQL-Abfrage

Verwendete Parameter

Erklärung der CTEs (Common Table Expressions)

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Alle Melde-Daten

Erklärung der SQL-Abfrage

Verwendete Parameter

Erklärung der CTEs (Common Table Expressions)

Erklärung der Ergebnisse

Beispielhafte Ergebnisse (Anonymisiert)

Beispielhafte Ergebnisse (Anonymisiert)

Eingereichte Melde-Berichte

Verwendete Parameter

Erklärung der CTEs

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Sperren und Stummschaltungen:

Verwendete Parameter

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Durchgeführte Maßnahmen bei akzeptierten Meldungen

Verwendete Parameter

Erklärung der CTEs

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Durchgeführte Moderationsmaßnahmen

Verwendete Parameter

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Individuelle durchgeführte Moderationsmaßnahmen

Verwendete Parameter

Erklärung der Ergebnisse

Beispielhafte Ergebnisse

Verwandte Themen