Analisi delle attività di moderazione e segnalazione

SaraDev · 13 Febbraio 2025, 11:08pm

Mantenere una comunità sana e inclusiva richiede una moderazione efficace, che può includere la revisione dei post segnalati, l’analisi delle prestazioni dei moderatori e la gestione dei contenuti degli utenti.

Questa guida contiene una varietà di report SQL per Discourse progettati per aiutare ad analizzare le attività relative alla moderazione.

In questo argomento troverai query dettagliate per Data Explorer per:

Statistiche sulla risoluzione delle segnalazioni.
Percentuali di risoluzione degli elementi da revisionare.
Metriche di prestazioni specifiche per i moderatori.
Informazioni sulle attività di segnalazione degli utenti.
Dati completi di tutte le azioni relative a utenti, post e argomenti segnalati

Percentuale di Risoluzione delle Segnalazioni ai Post per Tipo

Spiegazione della Query SQL

Questa query calcola la percentuale di risoluzioni (acconsentite, non acconsentite, differite, eliminate) per i post segnalati, raggruppate per tipo di segnalazione.

-- [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 -- tempo di risoluzione in minuti
    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

Parametri Utilizzati

:start_date: La data di inizio per filtrare i post segnalati.
:end_date: La data di fine per filtrare i post segnalati.

Spiegazione delle CTE

period_actions: Filtra i post segnalati all’interno dell’intervallo di date specificato e calcola il tempo di risoluzione per ogni segnalazione.
flag_types: Mappa gli ID dei tipi di segnalazione a nomi leggibili (es. off-topic, inappropriate, spam).
flag_resolutions: Raggruppa le segnalazioni per tipo e risoluzione (acconsentita, non acconsentita, differita, eliminata) e conta le occorrenze di ogni risoluzione.
flag_totals: Calcola il numero totale di segnalazioni per ogni tipo di segnalazione.
resolution_percentages: Combina i conteggi delle risoluzioni e il totale delle segnalazioni per calcolare la percentuale di ogni tipo di risoluzione per ogni tipo di segnalazione.
pivoted_data: Pivota i dati per visualizzare le percentuali e i conteggi di risoluzione in colonne separate per ogni tipo di risoluzione.

Spiegazione dei Risultati

Il risultato finale è una tabella che mostra:

Tipo di segnalazione (es. off-topic, spam).
Percentuali e conteggi per ogni tipo di risoluzione (acconsentita, non acconsentita, differita, eliminata).
Totale segnalazioni per ogni tipo di segnalazione.

Esempio di Risultati

Tipo di Segnalazione	% Acconsentite	Conteggio Acconsentite	% Non Acconsentite	Conteggio Non Acconsentite	% Differite	Conteggio Differite	% Eliminate	Conteggio Eliminate	Totale Segnalazioni
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

Percentuali di Risoluzione degli Elementi da Revisionare

Spiegazione della Query SQL

Questa query analizza gli stati di risoluzione degli elementi da revisionare (es. post segnalati) all’interno di un dato intervallo di date. Calcola la percentuale e il conteggio di ogni stato di risoluzione per ogni tipo di segnalazione.

-- [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,
    -- Percentuali
    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,
    -- Conteggi
    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

Parametri Utilizzati

:start_date: La data di inizio per filtrare gli elementi da revisionare.
:end_date: La data di fine per filtrare gli elementi da revisionare.

Spiegazione delle CTE

flag_data: Raggruppa gli elementi da revisionare per tipo di segnalazione e stato di risoluzione, contando le occorrenze di ogni combinazione.
flag_totals: Calcola il numero totale di segnalazioni per ogni tipo di segnalazione.
flag_percentages: Combina i conteggi delle segnalazioni e i totali per calcolare la percentuale di ogni stato di risoluzione per ogni tipo di segnalazione.

Spiegazione dei Risultati

Il risultato finale è una tabella che mostra:

Tipo di segnalazione.
Percentuali e conteggi per ogni stato di risoluzione (in sospeso, approvato, rifiutato, ignorato, eliminato).

Esempio di Risultati

Tipo di Segnalazione	% In Sospeso	Conteggio In Sospeso	% Approvato	Conteggio Approvato	% Rifiutato	Conteggio Rifiutato	% Ignorato	Conteggio Ignorato	% Eliminato	Conteggio Eliminato
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

Risoluzioni delle Segnalazioni da parte dei Moderatori

Spiegazione della Query SQL

Questa query fornisce informazioni sull’attività dei moderatori mostrando quali moderatori hanno risolto i post segnalati, i tipi di segnalazione gestiti e le risoluzioni applicate. Calcola percentuali e conteggi per ogni tipo di risoluzione.

-- [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 -- tempo di risoluzione in minuti
    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

Parametri Utilizzati

:start_date: La data di inizio per filtrare i post segnalati.
:end_date: La data di fine per filtrare i post segnalati.
:only_staff: Un parametro booleano per filtrare i risultati includendo solo i membri dello staff.

Spiegazione delle CTE

period_actions: Filtra i post segnalati all’interno dell’intervallo di date specificato e calcola il tempo di risoluzione per ogni segnalazione.
flag_types: Mappa gli ID dei tipi di segnalazione a nomi leggibili.
flag_resolutions: Raggruppa le segnalazioni per utente, tipo di segnalazione e risoluzione, contando le occorrenze di ogni combinazione.
flag_totals: Calcola il numero totale di segnalazioni per ogni utente e tipo di segnalazione.
resolution_percentages: Combina i conteggi delle risoluzioni e i totali per calcolare le percentuali per ogni tipo di risoluzione.
pivoted_data: Pivota i dati per visualizzare le percentuali e i conteggi di risoluzione in colonne separate per ogni tipo di risoluzione.

Spiegazione dei Risultati

Il risultato finale è una tabella che mostra:

Nome utente del moderatore.
Percentuali e conteggi per ogni tipo di risoluzione (acconsentita, non acconsentita, differita, eliminata).
Totale segnalazioni gestite da ogni moderatore.

Esempio di Risultati

Moderatore	Tipo di Segnalazione	% Acconsentite	Conteggio Acconsentite	% Non Acconsentite	Conteggio Non Acconsentite	% Differite	Conteggio Differite	% Eliminate	Conteggio Eliminate	Totale Segnalazioni
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

Chi Segnala i Post

Spiegazione della Query SQL

Questa query identifica gli utenti che hanno segnalato post all’interno di un intervallo di date specificato e calcola il numero totale di segnalazioni inviate da ogni utente.

-- [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) -- tipi di segnalazione
  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

Parametri Utilizzati

:start_date: La data di inizio per filtrare i post segnalati.
:end_date: La data di fine per filtrare i post segnalati.
:only_staff: Un parametro booleano per filtrare i risultati includendo solo i membri dello staff.

Spiegazione dei Risultati

Il risultato finale è un elenco classificato di utenti con i rispettivi conteggi di segnalazioni.

Esempio di Risultati

ID Utente	Nome Utente	Conteggio Segnalazioni
1	user1	50
2	user2	30
3	user3	20

Note Utente

Spiegazione della Query SQL

Questa query recupera le note utente archiviate nella tabella plugin_store_rows. Estrae dettagli come l’ID utente, la data di creazione, il contenuto della nota e l’ID del creatore.

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

Parametri Utilizzati

:start_date: La data di inizio per filtrare le note utente.
:end_date: La data di fine per filtrare le note utente.

Spiegazione dei Risultati

Il risultato finale è un elenco dettagliato di note utente con i relativi metadati.

Esempio di Risultati

ID Utente	Creato Il	Nota Utente	ID Utente Creatore
1	2025-01-01	Questo utente è utile.	2
2	2025-02-01	Questo utente è associato a due altri account utente.	3

KPI Moderatori - Segnalazioni e Tempo Medio di Risoluzione

Spiegazione della Query SQL

Questa query valuta le prestazioni dei moderatori calcolando il numero di segnalazioni gestite e il tempo medio di risoluzione (in minuti) per ogni moderatore.

-- [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 -- tempo di risoluzione in minuti
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- Tipi di segnalazione
      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

Parametri Utilizzati

:start_date: La data di inizio per filtrare i post segnalati.
:end_date: La data di fine per filtrare i post segnalati.

Spiegazione delle CTE

period_actions: Filtra i post segnalati all’interno dell’intervallo di date specificato e calcola il tempo di risoluzione per ogni segnalazione.
moderator_actions: Identifica le segnalazioni risolte dai moderatori e calcola il tempo di risoluzione per ogni segnalazione.
moderator_stats: Raggruppa le segnalazioni per moderatore e calcola il numero totale di segnalazioni gestite e il tempo medio di risoluzione.

Spiegazione dei Risultati

Il risultato finale è un elenco classificato di moderatori con i rispettivi conteggi di segnalazioni gestite e tempi medi di risoluzione.

Esempio di Risultati

Nome Utente Moderatore	Segnalazioni Gestite	Tempo Medio di Risoluzione (Minuti)
mod1	50	15.00
mod2	30	20.00

Tutti i Dati delle Segnalazioni

Spiegazione della Query SQL

Questa query fornisce un dataset completo di tutti i dati relativi a utenti, post e argomenti segnalati all’interno di un intervallo di date specificato. Combina dati da più tabelle per includere dettagli come il tipo di segnalazione, l’elemento segnalato, il motivo della segnalazione, la fonte della segnalazione, la decisione di risoluzione e i messaggi correlati.

-- [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 -- Includi solo le segnalazioni a cui è stato acconsentito e per cui è stata intrapresa un'azione
),
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, -- Rimuove solo gli URL
    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 -- Differenza di tempo in minuti
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

Parametri Utilizzati

:start_date: La data di inizio per filtrare i post segnalati.
:end_date: La data di fine per filtrare i post segnalati.

Spiegazione delle CTE

flag_data: Recupera informazioni dettagliate sui post segnalati, inclusi l’ID della segnalazione, l’elemento segnalato, il tipo di segnalazione, il motivo della segnalazione, la fonte della segnalazione e i dettagli della revisione.
review_decisions: Mappa i codici di stato di revisione a nomi di decisione leggibili (es. in sospeso, acconsentita, non acconsentita, ignorata).
flag_types: Mappa gli ID dei tipi di azione sui post a nomi di tipo di segnalazione leggibili (es. off-topic, inappropriate, spam).

Spiegazione dei Risultati

ID Segnalazione: L’identificatore univoco della segnalazione.
Elemento Segnalato: L’ID del post segnalato.
Nome Utente Segnalante: Il nome utente dell’utente che ha segnalato il post.
Data Segnalazione: La data in cui è stata creata la segnalazione.
Tipo di Segnalazione: Il tipo numerico della segnalazione.
Nome Tipo di Segnalazione: Il nome leggibile del tipo di segnalazione (es. off-topic, spam).
Revisionabile dal Moderatore: Indica se la segnalazione è stata sollevata da un utente o dal sistema.
Motivo Segnalazione: Il motivo fornito per la segnalazione.
Testo Elemento Segnalato: Il contenuto del post segnalato.
ID Messaggio Correlato: L’ID di eventuali messaggi correlati (se applicabile).
Testo Messaggio Correlato: Il contenuto del messaggio correlato, con gli URL rimossi per chiarezza.
Nome Utente Revisore: Il nome utente del moderatore che ha revisionato la segnalazione.
Decisione Revisione: La decisione presa dal revisore (es. acconsentita, non acconsentita, ignorata, eliminata).
Azione Intrapresa: L’azione intrapresa a seguito della segnalazione, come silenziare o sospendere l’utente, eliminare o nascondere il post, o nessuna azione.
Tempo Revisione (Minuti): Il tempo impiegato per revisionare la segnalazione, calcolato come la differenza tra il tempo di creazione della segnalazione e il tempo di revisione, in minuti.

Esempio di Risultati (Anonimizzati)

ID Segnalazione	Elemento Segnalato	Nome Utente Segnalante	Data Segnalazione	Tipo di Segnalazione	Nome Tipo di Segnalazione	Revisionabile dal Moderatore	Motivo Segnalazione	Testo Elemento Segnalato	ID Messaggio Correlato	Testo Messaggio Correlato	Nome Utente Revisore	Decisione Revisione	Azione Intrapresa	Tempo Revisione (Minuti)
12345	67890	user123	2025-04-01 12:00	8	Spam	true	Contenuto spam	“Compra ora su spam.com”	98765	“Guarda questo!”	mod456	Acconsentita	Post eliminato	15.25
12346	67891	user124	2025-04-02 14:30	4	Inappropriato	false	Offensivo	“Questo è inappropriato!”	NULL	NULL	mod457	Non Acconsentita	Nessuna azione intrapresa	30.50

Report Segnalazioni Inviati

Questo report fornisce una panoramica delle segnalazioni inviate all’interno dell’intervallo di date specificato. Categorizza le segnalazioni per tipo (es. Spam, Inappropriato) e distingue tra segnalazioni segnalate dagli utenti e segnalazioni generate dal sistema. Il report include il numero totale di segnalazioni per ogni tipo, aiutando a identificare i problemi più comuni segnalati sulla piattaforma.

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

Parametri Utilizzati

:start_date: La data di inizio per il filtraggio dei post segnalati.
:end_date: La data di fine per il filtraggio dei post segnalati.

Spiegazione delle CTE

flag_data:
Questa CTE recupera informazioni dettagliate sui post segnalati, inclusi:

L’ID univoco del segnalazione (flag_id), l’ID del post segnalato (post_id) e l’argomento a cui appartiene (topic_id).
Informazioni sulla segnalazione stessa, come l’utente che l’ha effettuata (flagged_by_username), la data della segnalazione (flagged_date), il tipo di segnalazione (flag_type) e il motivo della segnalazione (flag_reason).
Dettagli sul processo di revisione, incluso il moderatore che ha revisionato la segnalazione (reviewed_by_username), la decisione di revisione (review_status) e l’orario di revisione (reviewed_at).

flag_types:
Questa CTE mappa gli ID numerici dei tipi di azione sui post a nomi di tipi di segnalazione leggibili dall’utente:

3: Fuori argomento
4: Inappropriato
6: Notifica utente
7: Notifica moderatori
8: Spam
10: Illegale
NULL: Altro

Spiegazione dei Risultati

La query finale aggrega i dati delle segnalazioni per tipo di segnalazione e fornisce le seguenti metriche:

Tipo: Il nome leggibile dall’utente del tipo di segnalazione (es. Fuori argomento, Spam).
Segnalato: Il conteggio delle segnalazioni inviate dagli utenti (escludendo le segnalazioni generate dal sistema).
Automatizzato: Il conteggio delle segnalazioni generate dal sistema o dai bot (es. spam_scanner_bot, system).
Totale: Il numero totale di segnalazioni per ciascun tipo.

I risultati sono ordinati per numero totale di segnalazioni in ordine decrescente.

Esempio di Risultati

Tipo	Segnalato	Automatizzato	Totale
Spam	120	80	200
Inappropriato	90	10	100
Fuori argomento	60	5	65
Notifica_moderatori	30	0	30
Illegale	10	2	12
Altro	5	0	5

Ban e Sospensioni:

Questo report elenca gli utenti che sono stati sospesi o silenziati nell’intervallo di date specificato. Include dettagli come le date di sospensione o silenziamento, la durata dell’azione e le date di creazione dell’account e di ultima attività dell’utente. Questo report è utile per monitorare le azioni di moderazione e identificare modelli di comportamento degli utenti che portano a ban o silenziamenti.

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

Parametri Utilizzati

:start_date: La data di inizio per il filtraggio degli utenti sospesi o silenziati.
:end_date: La data di fine per il filtraggio degli utenti sospesi o silenziati.

Spiegazione dei Risultati

Questa query recupera un elenco di utenti che sono stati sospesi o silenziati nell’intervallo di date specificato. Le colonne chiave includono:

ID Utente: L’identificatore univoco per l’utente.
Nome Utente: Il nome utente dell’utente.
Nome: Il nome completo dell’utente (se disponibile).
Sospeso Il: La data in cui l’utente è stato sospeso.
Sospeso Fino Al: La data fino alla quale l’utente è sospeso.
Silenziato Fino Al: La data fino alla quale l’utente è silenziato.
Account Creato Il: La data in cui è stato creato l’account dell’utente.
Ultima Vista Il: L’ultima volta che l’utente è stato attivo sulla piattaforma.
Livello Segnalazione: Il livello di segnalazione corrente dell’utente.
Admin: Se l’utente è un amministratore (vero/falso).
Moderatore: Se l’utente è un moderatore (vero/falso).

I risultati sono ordinati per data di sospensione (suspended_at) e data di silenziamento (silenced_till) in ordine decrescente, con i valori nulli che appaiono per ultimi.

Esempio di Risultati

ID Utente	Nome Utente	Nome	Sospeso Il	Sospeso Fino Al	Silenziato Fino Al	Account Creato Il	Ultima Vista Il	Livello Segnalazione	Admin	Moderatore
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

Azioni di Segnalazione Concordate Effettuate

Questo report si concentra sulle segnalazioni che sono state concordate dai moderatori e hanno portato all’adozione di azioni. Categorizza le segnalazioni per tipo e fornisce metriche come il numero totale di segnalazioni, il tempo mediano impiegato per agire su di esse e i risultati (es. utenti silenziati, post eliminati).

-- [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 -- Include solo le segnalazioni concordate con azione intrapresa
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Fuori argomento' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriato' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notifica utente' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notifica moderatori' 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, 'Illegale' 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 i tipi di segnalazione mappati
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- Include tipi di segnalazione specifici per risultati NULL
        )
    GROUP BY
        COALESCE(ft.flag_type_name, fd.flag_type)
)
SELECT
    COALESCE(ft.flag_type_name, fd.flag_type) AS Tipo,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Segnalato,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automatizzato,
    COUNT(*) AS Totale,
    COALESCE(mta.median_time_minutes, 0) AS "Tempo mediano di azione (minuti)",
    COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "Utente silenziato",
    COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "Utente eliminato",
    COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post eliminato",
    COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post nascosto"

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 i tipi di segnalazione mappati
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- Include tipi di segnalazione specifici per risultati NULL
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Totale DESC

Parametri Utilizzati

:start_date: La data di inizio per il filtraggio delle segnalazioni concordate.
:end_date: La data di fine per il filtraggio delle segnalazioni concordate.

Spiegazione delle CTE

flag_data:
Questa CTE recupera informazioni dettagliate sulle segnalazioni che sono state concordate e hanno portato all’adozione di azioni. Include:

L’ID univoco della segnalazione (flag_id), l’ID del post segnalato (post_id) e l’argomento a cui appartiene (topic_id).
Informazioni sulla segnalazione stessa, come l’utente che l’ha effettuata (flagged_by_username), la data della segnalazione (flagged_date), il tipo di segnalazione (flag_type) e il motivo della segnalazione (flag_reason).
Dettagli sul processo di revisione, incluso il moderatore che ha revisionato la segnalazione (reviewed_by_username), la decisione di revisione (review_status) e l’orario di revisione (reviewed_at).
Informazioni aggiuntive sul post segnalato, come se è stato eliminato, nascosto o se l’autore è stato silenziato o sospeso.

flag_types:
Questa CTE mappa gli ID numerici dei tipi di azione sui post a nomi di tipi di segnalazione leggibili dall’utente:

3: Fuori argomento
4: Inappropriato
6: Notifica utente
7: Notifica moderatori
8: Spam
10: Illegale

median_time_to_act:
Questa CTE calcola il tempo mediano (in minuti) impiegato per agire su ciascun tipo di segnalazione. Il tempo è calcolato come differenza tra il tempo di creazione della segnalazione (flagged_date) e il tempo di revisione (reviewed_at).

Spiegazione dei Risultati

La query finale aggrega i dati delle segnalazioni concordate per tipo di segnalazione e fornisce le seguenti metriche:

Tipo: Il nome leggibile dall’utente del tipo di segnalazione (es. Fuori argomento, Spam).
Segnalato: Il conteggio delle segnalazioni inviate dagli utenti (escludendo le segnalazioni generate dal sistema).
Automatizzato: Il conteggio delle segnalazioni generate dal sistema o dai bot (es. spam_scanner_bot, system).
Totale: Il numero totale di segnalazioni per ciascun tipo.
Tempo Mediano di Azione (Minuti): Il tempo mediano impiegato per agire sulle segnalazioni di questo tipo, in minuti.
Utente Silenziato: Il conteggio delle segnalazioni che hanno portato al silenziamento dell’utente.
Utente Eliminato: Il conteggio delle segnalazioni che hanno portato alla sospensione dell’utente.
Post Eliminato: Il conteggio delle segnalazioni che hanno portato all’eliminazione del post.
Post Nascosto: Il conteggio delle segnalazioni che hanno portato al nascondimento del post.

I risultati sono ordinati per numero totale di segnalazioni in ordine decrescente.

Esempio di Risultati

Tipo	Segnalato	Automatizzato	Totale	Tempo Mediano di Azione (Minuti)	Utente Silenziato	Utente Eliminato	Post Eliminato	Post Nascosto
Spam	100	50	150	30	20	10	50	30
Inappropriato	80	5	85	45	15	5	30	20
Fuori argomento	40	2	42	25	5	0	10	15
Notifica_moderatori	20	0	20	60	0	0	5	10
Illegale	5	1	6	120	1	1	3	2

Azioni di Moderazione Effettuate

Questo report fornisce un riepilogo delle azioni di moderazione effettuate nell’intervallo di date specificato. Aggrega vari tipi di azioni concordate, inclusi contenuti segnalati da utenti o automazione, post eliminati o nascosti, avvisi emessi, account eliminati o sospesi e utenti silenziati per periodi prolungati. Ogni categoria è presentata con il numero totale di casi, offrendo una panoramica ad alto livello dell’attività di moderazione.

-- [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 -- Include solo le segnalazioni concordate con azione intrapresa
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Fuori argomento' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriato' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notifica utente' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notifica moderatori' 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, 'Illegale' 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 i tipi di segnalazione mappati
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- Include tipi di segnalazione specifici per risultati NULL
),
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
    'Contenuto segnalato da utenti' AS categoria,
    fc.user_flagged AS "Numero di Casi"
FROM flagged_content fc

UNION ALL

SELECT
    'Contenuto segnalato da automazione' AS categoria,
    fc.automation_flagged AS "Numero di Casi"
FROM flagged_content fc

UNION ALL

SELECT
    'Post eliminati per violazione dei termini' AS categoria,
    pdh.posts_deleted AS "Numero di Casi"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Post nascosti' AS categoria,
    pdh.posts_hidden AS "Numero di Casi"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Avvisi EMESSI' AS categoria,
    wi.warnings_count AS "Numero di Casi"
FROM warnings_issued wi

UNION ALL

SELECT
    'Account eliminati' AS categoria,
    vs.accounts_deleted AS "Numero di Casi"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Account sospesi' AS categoria,
    vs.accounts_suspended AS "Numero di Casi"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Utenti silenziati per 10+ anni' AS categoria,
    si.silences_count AS "Numero di Casi"
FROM silences_issued si

Parametri Utilizzati

:start_date: La data di inizio per il filtraggio delle azioni di moderazione.
:end_date: La data di fine per il filtraggio delle azioni di moderazione.

Spiegazione dei Risultati

La query aggrega le azioni di moderazione in categorie e fornisce il numero totale di casi per ciascuna categoria. Le categorie includono:

Contenuto segnalato da utenti: Il numero di post segnalati dagli utenti regolari (escludendo le segnalazioni generate dal sistema).
Contenuto segnalato da automazione: Il numero di post segnalati da sistemi automatizzati o bot (es. spam_scanner_bot, system).
Post eliminati per violazione dei termini: Il numero di post che sono stati eliminati a causa di violazioni delle linee guida della comunità o dei termini di servizio.
Post nascosti: Il numero di post che sono stati nascosti (ma non eliminati) per vari motivi.
Avvisi EMESSI: Il numero di avvisi emessi agli utenti per comportamenti o contenuti inappropriati.
Account eliminati: Il numero di account utente eliminati a causa di violazioni, come essere segnalati come spammer o rifiutati nelle code di revisione.
Account sospesi: Il numero di account utente sospesi per un periodo specifico a causa di violazioni.
Utenti silenziati per 10+ anni: Il numero di utenti silenziati indefinitamente o per periodi prolungati (10+ anni).

Esempio di Risultati

Categoria	Numero di Casi
Contenuto segnalato da utenti	150
Contenuto segnalato da automazione	100
Post eliminati per violazione dei termini	50
Post nascosti	30
Avvisi EMESSI	20
Account eliminati	10
Account sospesi	15
Utenti silenziati per 10+ anni	5

Azioni di Moderazione Individuali Effettuate

Questo report fornisce un registro dettagliato delle azioni di moderazione individuali concordate effettuate nell’intervallo di date specificato. Include informazioni sull’utente che ha eseguito l’azione, l’utente target, la data dell’azione, la categoria dell’azione (es. contenuto segnalato, post eliminati, avvisi emessi) e il contesto o il motivo dell’azione. Questo report è utile per auditare decisioni specifiche di moderazione e comprendere il contesto dietro ciascuna azione.

-- [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 -- Include solo le segnalazioni concordate con azione intrapresa
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Fuori argomento' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriato' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notifica utente' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notifica moderatori' 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, 'Illegale' 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,
        'Contenuto segnalato da utenti' AS categoria,
        fd.flagged_item_text AS contesto
    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,
        'Contenuto segnalato da automazione' AS categoria,
        fd.flagged_item_text AS contesto
    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,
        'Post eliminati per violazione dei termini' AS categoria,
        fd.flagged_item_text AS contesto
    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,
        'Post nascosti' AS categoria,
        fd.flagged_item_text AS contesto
    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,
        'Avvisi EMESSI' AS categoria,
        NULL AS contesto
    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,
        'Account eliminati' AS categoria,
        uh.context AS contesto
    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,
        'Account sospesi' AS categoria,
        uh.context AS contesto
    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,
        'Utenti silenziati per 10+ anni' AS categoria,
        uh.context AS contesto
    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,
    categoria,
    contesto
FROM flagged_content

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    categoria,
    contesto
FROM posts_deleted_and_hidden

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    categoria,
    contesto
FROM warnings_issued

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    categoria,
    contesto
FROM violations_and_suspensions

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    categoria,
    contesto
FROM silences_issued

Parametri Utilizzati

:start_date: La data di inizio per il filtraggio delle azioni di moderazione individuali.
:end_date: La data di fine per il filtraggio delle azioni di moderazione individuali.

Spiegazione dei Risultati

La query fornisce un registro dettagliato delle azioni di moderazione individuali, inclusi:

Utente Attuante: Il nome utente del moderatore, del sistema o dell’utente che ha eseguito l’azione.
Utente Target: Il nome utente o l’ID dell’utente che era il soggetto dell’azione (es. l’autore di un post segnalato o il destinatario di un avviso).
Data Azione: La data e l’ora in cui si è verificata l’azione.
Categoria: Il tipo di azione di moderazione, come:

Contenuto segnalato da utenti
Contenuto segnalato da automazione
Post eliminati per violazione dei termini
Post nascosti
Avvisi EMESSI
Account eliminati
Account sospesi
Utenti silenziati per 10+ anni

Contesto: Informazioni aggiuntive o contenuti relativi all’azione, come il testo di un post segnalato o il motivo di una sospensione.

Esempio di Risultati

Utente Attuante	Utente Target	Data Azione	Categoria	Contesto
user123	user456	2024-02-01 10:00	Contenuto segnalato da utenti	“Questo post contiene contenuti spam.”
spam_scanner	user789	2024-02-02 12:00	Contenuto segnalato da automazione	“Rilevato come spam dal sistema.”
mod001	user456	2024-02-03 14:00	Post eliminati per violazione dei termini	“Contenuto Esempio Post
mod002	user123	2024-02-04 16:00	Post nascosti	“Post considerato inappropriato.”
admin001	user789	2024-02-05 18:00	Avvisi EMESSI	NULL
admin002	user456	2024-02-06 20:00	Account eliminati	“Account eliminato tramite coda di revisione.”
mod003	user123	2024-02-07 22:00	Account sospesi	“Utente sospeso per spam ripetuto.”
admin003	user789	2024-02-08 08:00	Utenti silenziati per 10+ anni	“Utente silenziato per violazioni estreme.”

Argomento		Risposte	Visualizzazioni
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	430	Dicembre 22, 2023
Dashboard Report - User Flagging Ratio Data & reporting sql-query , dashboard-reports , dashboard-sql	0	353	Febbraio 6, 2024
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	782	Ottobre 30, 2024
Statistics on spam flags reported by AI triage automation Data & reporting ai , sql-query , spam	3	144	Maggio 31, 2025
Show what action was taken and by who for Flags Feature	2	909	Aprile 12, 2014

Analisi delle attività di moderazione e segnalazione

Percentuale di Risoluzione delle Segnalazioni ai Post per Tipo

Spiegazione della Query SQL

Parametri Utilizzati

Spiegazione delle CTE

Spiegazione dei Risultati

Esempio di Risultati

Percentuali di Risoluzione degli Elementi da Revisionare

Spiegazione della Query SQL

Parametri Utilizzati

Spiegazione delle CTE

Spiegazione dei Risultati

Esempio di Risultati

Risoluzioni delle Segnalazioni da parte dei Moderatori

Spiegazione della Query SQL

Parametri Utilizzati

Spiegazione delle CTE

Spiegazione dei Risultati

Esempio di Risultati

Chi Segnala i Post

Spiegazione della Query SQL

Parametri Utilizzati

Spiegazione dei Risultati

Esempio di Risultati

Note Utente

Spiegazione della Query SQL

Parametri Utilizzati

Spiegazione dei Risultati

Esempio di Risultati

KPI Moderatori - Segnalazioni e Tempo Medio di Risoluzione

Spiegazione della Query SQL

Parametri Utilizzati

Spiegazione delle CTE

Spiegazione dei Risultati

Esempio di Risultati

Tutti i Dati delle Segnalazioni

Spiegazione della Query SQL

Parametri Utilizzati

Spiegazione delle CTE

Spiegazione dei Risultati

Esempio di Risultati (Anonimizzati)

Esempio di Risultati (Anonimizzati)

Report Segnalazioni Inviati

Parametri Utilizzati

Spiegazione delle CTE

Spiegazione dei Risultati

Esempio di Risultati

Ban e Sospensioni:

Parametri Utilizzati

Spiegazione dei Risultati

Esempio di Risultati

Azioni di Segnalazione Concordate Effettuate

Parametri Utilizzati

Spiegazione delle CTE

Spiegazione dei Risultati

Esempio di Risultati

Azioni di Moderazione Effettuate

Parametri Utilizzati

Spiegazione dei Risultati

Esempio di Risultati

Azioni di Moderazione Individuali Effettuate

Parametri Utilizzati

Spiegazione dei Risultati

Esempio di Risultati

Argomenti correlati