Analyse des rapports d'activité de modération et de signalement

SaraDev · Février 13, 2025, 11:08

Le maintien d’une communauté saine et inclusive nécessite une modération efficace, qui peut inclure la révision des publications signalées, l’analyse des performances des modérateurs et la gestion du contenu des utilisateurs.

Ce guide contient une variété de rapports SQL pour Discourse conçus pour aider à analyser les activités liées à la modération.

Dans ce sujet, vous trouverez des requêtes Data Explorer détaillées pour :

Statistiques de résolution des signalements.
Pourcentages de résolution des éléments à examiner.
Métriques de performance spécifiques aux modérateurs.
Informations sur l’activité de signalement des utilisateurs.
Données complètes de toutes les actions signalées sur les utilisateurs, les publications et les sujets

Pourcentage de résolution des signalements de publication par type

Explication de la requête SQL

Cette requête calcule le pourcentage de résolutions (acceptées, rejetées, différées, supprimées) pour les publications signalées, regroupées par type de signalement.

-- [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 -- temps de résolution en minutes
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8)
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
flag_types AS (
    SELECT pat.id,
           CASE 
               WHEN pat.id = 3 THEN 'hors_sujet'
               WHEN pat.id = 4 THEN 'inapproprié'
               WHEN pat.id = 6 THEN 'notifier_utilisateur'
               WHEN pat.id = 7 THEN 'notifier_modérateurs'
               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 'accepté'
            WHEN pa.disagreed_at IS NOT NULL THEN 'rejeté'
            WHEN pa.deferred_at IS NOT NULL THEN 'différé'
            WHEN pa.deleted_at IS NOT NULL THEN 'supprimé'
        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 = 'accepté' THEN resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN resolution = 'rejeté' THEN resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN resolution = 'différé' THEN resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN resolution = 'supprimé' THEN resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN resolution = 'accepté' THEN resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN resolution = 'rejeté' THEN resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN resolution = 'différé' THEN resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN resolution = 'supprimé' 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

Paramètres utilisés

:start_date : La date de début pour filtrer les publications signalées.
:end_date : La date de fin pour filtrer les publications signalées.

Explication des CTE (Common Table Expressions)

period_actions : Filtre les publications signalées dans la plage de dates spécifiée et calcule le temps de résolution pour chaque signalement.
flag_types : Associe les identifiants de type de signalement à des noms lisibles (par exemple, hors-sujet, inapproprié, spam).
flag_resolutions : Regroupe les signalements par type et résolution (accepté, rejeté, différé, supprimé) et compte les occurrences de chaque résolution.
flag_totals : Calcule le nombre total de signalements pour chaque type de signalement.
resolution_percentages : Combine les comptes de résolution et les totaux pour calculer le pourcentage de chaque type de résolution pour chaque type de signalement.
pivoted_data : Pivote les données pour afficher les pourcentages et les comptes de résolution dans des colonnes séparées pour chaque type de résolution.

Explication des résultats

Le résultat final est un tableau montrant :

Type de signalement (par exemple, hors-sujet, spam).
Pourcentages et comptes pour chaque type de résolution (accepté, rejeté, différé, supprimé).
Total des signalements pour chaque type de signalement.

Exemples de résultats

Type de signalement	% Accepté	Nb Accepté	% Rejeté	Nb Rejeté	% Différé	Nb Différé	% Supprimé	Nb Supprimé	Total Signalements
hors_sujet	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

Pourcentages de résolution des éléments à examiner

Explication de la requête SQL

Cette requête analyse les statuts de résolution des éléments à examiner (par exemple, publications signalées) dans une plage de dates donnée. Elle calcule le pourcentage et le compte de chaque statut de résolution pour chaque type de signalement.

-- [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,
    -- Pourcentages
    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,
    -- Comptes
    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

Paramètres utilisés

:start_date : La date de début pour filtrer les éléments à examiner.
:end_date : La date de fin pour filtrer les éléments à examiner.

Explication des CTE

flag_data : Regroupe les éléments à examiner par type de signalement et statut de résolution, en comptant les occurrences de chaque combinaison.
flag_totals : Calcule le nombre total de signalements pour chaque type de signalement.
flag_percentages : Combine les comptes de signalements et les totaux pour calculer le pourcentage de chaque statut de résolution pour chaque type de signalement.

Explication des résultats

Le résultat final est un tableau montrant :

Type de signalement.
Pourcentages et comptes pour chaque statut de résolution (en attente, approuvé, rejeté, ignoré, supprimé).

Exemples de résultats

Type de signalement	% En attente	Nb En attente	% Approuvé	Nb Approuvé	% Rejeté	Nb Rejeté	% Ignoré	Nb Ignoré	% Supprimé	Nb Supprimé
hors_sujet	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

Résolutions des signalements par les modérateurs

Explication de la requête SQL

Cette requête fournit des informations sur l’activité des modérateurs en montrant quels modérateurs ont résolu les publications signalées, les types de signalements qu’ils ont traités et les résolutions qu’ils ont appliquées. Elle calcule les pourcentages et les comptes pour chaque type de résolution.

-- [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 -- temps de résolution en minutes
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8)
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
flag_types AS (
    SELECT 
        pat.id,
        CASE 
            WHEN pat.id = 3 THEN 'hors_sujet'
            WHEN pat.id = 4 THEN 'inapproprié'
            WHEN pat.id = 6 THEN 'notifier_utilisateur'
            WHEN pat.id = 7 THEN 'notifier_modérateurs'
            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 'accepté'
            WHEN pa.disagreed_at IS NOT NULL THEN 'rejeté'
            WHEN pa.deferred_at IS NOT NULL THEN 'différé'
            WHEN pa.deleted_at IS NOT NULL THEN 'supprimé'
        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 = 'accepté' THEN rp.resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN rp.resolution = 'rejeté' THEN rp.resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN rp.resolution = 'différé' THEN rp.resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN rp.resolution = 'supprimé' THEN rp.resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN rp.resolution = 'accepté' THEN rp.resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN rp.resolution = 'rejeté' THEN rp.resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN rp.resolution = 'différé' THEN rp.resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN rp.resolution = 'supprimé' 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

Paramètres utilisés

:start_date : La date de début pour filtrer les publications signalées.
:end_date : La date de fin pour filtrer les publications signalées.
:only_staff : Un paramètre booléen pour filtrer les résultats afin d’inclure uniquement les membres du personnel.

Explication des CTE

period_actions : Filtre les publications signalées dans la plage de dates spécifiée et calcule le temps de résolution pour chaque signalement.
flag_types : Associe les identifiants de type de signalement à des noms lisibles.
flag_resolutions : Regroupe les signalements par utilisateur, type de signalement et résolution, en comptant les occurrences de chaque combinaison.
flag_totals : Calcule le nombre total de signalements pour chaque utilisateur et type de signalement.
resolution_percentages : Combine les comptes de résolution et les totaux pour calculer les pourcentages pour chaque type de résolution.
pivoted_data : Pivote les données pour afficher les pourcentages et les comptes de résolution dans des colonnes séparées pour chaque type de résolution.

Explication des résultats

Le résultat final est un tableau montrant :

Nom d’utilisateur du modérateur.
Pourcentages et comptes pour chaque type de résolution (accepté, rejeté, différé, supprimé).
Total des signalements traités par chaque modérateur.

Exemples de résultats

Modérateur	Type de signalement	% Accepté	Nb Accepté	% Rejeté	Nb Rejeté	% Différé	Nb Différé	% Supprimé	Nb Supprimé	Total Signalements
mod1	hors_sujet	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

Qui signale les publications

Explication de la requête SQL

Cette requête identifie les utilisateurs qui ont signalé des publications dans une plage de dates spécifiée et calcule le nombre total de signalements soumis par chaque utilisateur.

-- [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) -- types de signalements
  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

Paramètres utilisés

:start_date : La date de début pour filtrer les publications signalées.
:end_date : La date de fin pour filtrer les publications signalées.
:only_staff : Un paramètre booléen pour filtrer les résultats afin d’inclure uniquement les membres du personnel.

Explication des résultats

Le résultat final est une liste classée d’utilisateurs avec leurs comptes de signalements.

Exemples de résultats

ID Utilisateur	Nom d’utilisateur	Nb Signalements
1	user1	50
2	user2	30
3	user3	20

Notes sur les utilisateurs

Explication de la requête SQL

Cette requête récupère les notes sur les utilisateurs stockées dans la table plugin_store_rows. Elle extrait des détails tels que l’ID de l’utilisateur, la date de création, le contenu de la note et l’ID du créateur.

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

Paramètres utilisés

:start_date : La date de début pour filtrer les notes sur les utilisateurs.
:end_date : La date de fin pour filtrer les notes sur les utilisateurs.

Explication des résultats

Le résultat final est une liste détaillée de notes sur les utilisateurs avec les métadonnées pertinentes.

Exemples de résultats

ID Utilisateur	Créé le	Note Utilisateur	ID Utilisateur Créé par
1	2025-01-01	Cet utilisateur est utile.	2
2	2025-02-01	Cet utilisateur est associé à deux autres comptes utilisateurs.	3

KPI des modérateurs - Signalements et temps moyen de résolution

Explication de la requête SQL

Cette requête évalue la performance des modérateurs en calculant le nombre de signalements traités et le temps moyen de résolution (en minutes) pour chaque modérateur.

-- [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 -- temps de résolution en minutes
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- Types de signalements
      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

Paramètres utilisés

:start_date : La date de début pour filtrer les publications signalées.
:end_date : La date de fin pour filtrer les publications signalées.

Explication des CTE

period_actions : Filtre les publications signalées dans la plage de dates spécifiée et calcule le temps de résolution pour chaque signalement.
moderator_actions : Identifie les signalements résolus par les modérateurs et calcule le temps de résolution pour chaque signalement.
moderator_stats : Regroupe les signalements par modérateur et calcule le nombre total de signalements traités et le temps moyen de résolution.

Explication des résultats

Le résultat final est une liste classée de modérateurs avec leurs comptes de signalements traités et leurs temps moyens de résolution.

Exemples de résultats

Nom d’utilisateur du modérateur	Signalements traités	Temps moyen de résolution (minutes)
mod1	50	15.00
mod2	30	20.00

Toutes les données de signalement

Explication de la requête SQL

Cette requête fournit un jeu de données complet de toutes les données signalées sur les utilisateurs, les publications et les sujets dans une plage de dates spécifiée. Elle combine des données provenant de plusieurs tables pour inclure des détails tels que le type de signalement, l’élément signalé, la raison du signalement, la source du signalement, la décision de résolution et les messages associés.

-- [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 -- Inclure uniquement les signalements acceptés avec action entreprise
),
review_decisions AS (
    SELECT
        0 AS status_code, 'en attente' AS decision_name
    UNION ALL
    SELECT
        1 AS status_code, 'accepté' AS decision_name
    UNION ALL
    SELECT
        2 AS status_code, 'rejeté' AS decision_name
    UNION ALL
    SELECT
        3 AS status_code, 'ignoré' AS decision_name
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'hors_sujet' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'inapproprié' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'notifier_utilisateur' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'notifier_modérateurs' 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, 'illégal' 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, -- Supprime uniquement les 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 'Utilisateur muet'
        WHEN fd.user_suspended_till IS NOT NULL THEN 'Utilisateur suspendu'
        WHEN fd.post_deleted_at IS NOT NULL THEN 'Publication supprimée'
        WHEN fd.post_hidden_at IS NOT NULL THEN 'Publication masquée'
        ELSE 'Aucune action entreprise'
    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 -- Différence de temps en minutes
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN posts rp 
    ON pa.related_post_id = rp.id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
LEFT JOIN review_decisions rd 
    ON fd.review_status = rd.status_code
ORDER BY
    fd.flagged_date DESC

Paramètres utilisés

:start_date : La date de début pour filtrer les publications signalées.
:end_date : La date de fin pour filtrer les publications signalées.

Explication des CTE

flag_data : Récupère des informations détaillées sur les publications signalées, y compris l’ID du signalement, l’élément signalé, le type de signalement, la raison du signalement, la source du signalement et les détails de l’examen.
review_decisions : Associe les codes de statut d’examen à des noms de décision lisibles (par exemple, en attente, accepté, rejeté, ignoré).
flag_types : Associe les identifiants de type d’action de publication à des noms de type de signalement lisibles (par exemple, hors-sujet, inapproprié, spam).

Explication des résultats

ID Signalement : L’identifiant unique du signalement.
Élément signalé : L’ID de la publication signalée.
Nom d’utilisateur ayant signalé : Le nom d’utilisateur de l’utilisateur qui a signalé la publication.
Date de signalement : La date de création du signalement.
Type de signalement : Le type numérique du signalement.
Nom du type de signalement : Le nom lisible du type de signalement (par exemple, hors-sujet, spam).
Examinable par modérateur : Indique si le signalement a été soumis par un utilisateur ou par le système.
Raison du signalement : La raison fournie pour le signalement.
Texte de l’élément signalé : Le contenu de la publication signalée.
ID du message associé : L’ID de tout message associé (le cas échéant).
Texte du message associé : Le contenu du message associé, avec les URL supprimées pour plus de clarté.
Nom d’utilisateur ayant examiné : Le nom d’utilisateur du modérateur qui a examiné le signalement.
Décision d’examen : La décision prise par l’examinateur (par exemple, accepté, rejeté, ignoré, supprimé).
Action entreprise : L’action entreprise à la suite du signalement, telle que le mutage ou la suspension de l’utilisateur, la suppression ou le masquage de la publication, ou aucune action.
Temps d’examen (minutes) : Le temps pris pour examiner le signalement, calculé comme la différence entre le temps de création du signalement et le temps d’examen, en minutes.

Exemples de résultats (anonymisés)

ID Signalement	Élément signalé	Nom d’utilisateur ayant signalé	Date de signalement	Type de signalement	Nom du type de signalement	Examinable par modérateur	Raison du signalement	Texte de l’élément signalé	ID du message associé	Texte du message associé	Nom d’utilisateur ayant examiné	Décision d’examen	Action entreprise	Temps d’examen (minutes)
12345	67890	user123	2025-04-01 12:00	8	Spam	true	Contenu spam	« Achetez maintenant sur spam.com »	98765	« Regardez ça ! »	mod456	Accepté	Publication supprimée	15.25
12346	67891	user124	2025-04-02 14:30	4	Inapproprié	false	Offensif	« C’est inapproprié ! »	NULL	NULL	mod457	Rejeté	Aucune action entreprise	30.50

Rapports de signalement soumis

Ce rapport fournit une vue d’ensemble des signalements soumis dans la plage de dates spécifiée. Il catégorise les signalements par leur type (par exemple, Spam, Inapproprié) et distingue entre les signalements rapportés par les utilisateurs et les signalements générés par le système. Le rapport inclut le nombre total de signalements pour chaque type, aidant à identifier les problèmes les plus couramment signalés sur la plateforme.

-- [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, 'Hors-sujet' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inapproprié' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notifier_utilisateur' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notifier_modérateurs' 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, 'Illégal' AS flag_type_name
    UNION ALL
    SELECT
        NULL AS post_action_type_id, 'Autre chose' 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 Rapporté,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automatisé,
    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

Paramètres utilisés

:start_date : La date de début pour filtrer les publications signalées.
:end_date : La date de fin pour filtrer les publications signalées.

Explication des CTE

flag_data :
Cette CTE récupère des informations détaillées sur les publications signalées, notamment :

L’identifiant unique du signalement (flag_id), l’identifiant de la publication signalée (post_id) et le sujet auquel elle appartient (topic_id).
Des informations sur le signalement lui-même, telles que l’utilisateur qui l’a effectué (flagged_by_username), la date du signalement (flagged_date), le type de signalement (flag_type) et la raison du signalement (flag_reason).
Des détails sur le processus de révision, incluant le modérateur qui a examiné le signalement (reviewed_by_username), la décision de révision (review_status) et l’heure de la révision (reviewed_at).

flag_types :
Cette CTE associe les identifiants numériques de type d’action sur publication à des noms de type de signalement lisibles par l’humain :

3 : Hors-sujet
4 : Inapproprié
6 :Notifier l’utilisateur
7 :Notifier les modérateurs
8 : Spam
10 : Illégal
NULL : Autre chose

Explication des résultats

La requête finale agrège les données de signalement par type de signalement et fournit les métriques suivantes :

Type : Le nom lisible par l’humain du type de signalement (par ex. Hors-sujet, Spam).
Signalé : Le nombre de signalements soumis par les utilisateurs (excluant les signalements générés par le système).
Automatisé : Le nombre de signalements générés par le système ou les bots (par ex. spam_scanner_bot, system).
Total : Le nombre total de signalements pour chaque type.

Les résultats sont triés par ordre décroissant du nombre total de signalements.

Exemple de résultats

Type	Signalé	Automatisé	Total
Spam	120	80	200
Inapproprié	90	10	100
Hors-sujet	60	5	65
Notifier_modérateurs	30	0	30
Illégal	10	2	12
Autre chose	5	0	5

Bannissements et suspensions :

Ce rapport liste les utilisateurs qui ont été suspendus ou réduits au silence dans la plage de dates spécifiée. Il inclut des détails tels que les dates de suspension ou de réduction au silence, la durée de la mesure, ainsi que les dates de création du compte et de dernière activité de l’utilisateur. Ce rapport est utile pour surveiller les actions de modération et identifier les schémas de comportement utilisateur menant à des bannissements ou réductions au silence.

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

Paramètres utilisés

:start_date : La date de début pour filtrer les utilisateurs suspendus ou réduits au silence.
:end_date : La date de fin pour filtrer les utilisateurs suspendus ou réduits au silence.

Explication des résultats

Cette requête récupère une liste d’utilisateurs qui ont été suspendus ou réduits au silence dans la plage de dates spécifiée. Les colonnes clés incluent :

ID utilisateur : L’identifiant unique de l’utilisateur.
Nom d’utilisateur : Le nom d’utilisateur de l’utilisateur.
Nom : Le nom complet de l’utilisateur (si disponible).
Suspendu le : La date à laquelle l’utilisateur a été suspendu.
Suspendu jusqu’au : La date jusqu’à laquelle l’utilisateur est suspendu.
Réduit au silence jusqu’au : La date jusqu’à laquelle l’utilisateur est réduit au silence.
Compte créé le : La date de création du compte de l’utilisateur.
Vu pour la dernière fois le : La dernière fois que l’utilisateur a été actif sur la plateforme.
Niveau de signalement : Le niveau de signalement actuel de l’utilisateur.
Admin : Si l’utilisateur est un administrateur (vrai/faux).
Modérateur : Si l’utilisateur est un modérateur (vrai/faux).

Les résultats sont triés par ordre décroissant de la date de suspension (suspended_at) et de la date de réduction au silence (silenced_till), les valeurs nulles apparaissant en dernier.

Exemple de résultats

ID utilisateur	Nom d’utilisateur	Nom	Suspendu le	Suspendu jusqu’au	Réduit au silence jusqu’au	Compte créé le	Vu pour la dernière fois le	Niveau de signalement	Admin	Modérateur
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

Actions de signalement validées

Ce rapport se concentre sur les signalements qui ont été validés par les modérateurs et ont entraîné des actions. Il catégorise les signalements par type et fournit des métriques telles que le nombre total de signalements, le temps médian nécessaire pour agir sur ceux-ci, et les résultats (par ex. utilisateurs réduits au silence, publications supprimées).

-- [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 -- Inclure uniquement les signalements validés et pour lesquels une action a été entreprise
),
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 -- Inclure les types de signalement mappés
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- Inclure les types de signalement spécifiques pour les résultats NULL
        )
    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 "Temps médian d'action (minutes)",
    COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "Utilisateur réduit au silence",
    COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "Utilisateur supprimé",
    COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Publication supprimée",
    COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Publication masquée"

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 -- Inclure les types de signalement mappés
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- Inclure les types de signalement spécifiques pour les résultats NULL
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

Paramètres utilisés

:start_date : La date de début pour filtrer les signalements validés.
:end_date : La date de fin pour filtrer les signalements validés.

Explication des CTE

flag_data :
Cette CTE récupère des informations détaillées sur les signalements qui ont été validés et pour lesquels des actions ont été entreprises. Elle inclut :

L’identifiant unique du signalement (flag_id), l’identifiant de la publication signalée (post_id) et le sujet auquel elle appartient (topic_id).
Des informations sur le signalement lui-même, telles que l’utilisateur qui l’a effectué (flagged_by_username), la date du signalement (flagged_date), le type de signalement (flag_type) et la raison du signalement (flag_reason).
Des détails sur le processus de révision, incluant le modérateur qui a examiné le signalement (reviewed_by_username), la décision de révision (review_status) et l’heure de la révision (reviewed_at).
Des informations supplémentaires sur la publication signalée, telles que si elle a été supprimée, masquée, ou si l’auteur a été réduit au silence ou suspendu.

flag_types :
Cette CTE associe les identifiants numériques de type d’action sur publication à des noms de type de signalement lisibles par l’humain :

3 : Hors-sujet
4 : Inapproprié
6 :Notifier l’utilisateur
7 :Notifier les modérateurs
8 : Spam
10 : Illégal

median_time_to_act :
Cette CTE calcule le temps médian (en minutes) nécessaire pour agir sur chaque type de signalement. Le temps est calculé comme la différence entre l’heure de création du signalement (flagged_date) et l’heure de révision (reviewed_at).

Explication des résultats

La requête finale agrège les données de signalement validé par type de signalement et fournit les métriques suivantes :

Type : Le nom lisible par l’humain du type de signalement (par ex. Hors-sujet, Spam).
Signalé : Le nombre de signalements soumis par les utilisateurs (excluant les signalements générés par le système).
Automatisé : Le nombre de signalements générés par le système ou les bots (par ex. spam_scanner_bot, system).
Total : Le nombre total de signalements pour chaque type.
Temps médian d’action (minutes) : Le temps médian nécessaire pour agir sur les signalements de ce type, en minutes.
Utilisateur réduit au silence : Le nombre de signalements ayant entraîné la réduction au silence de l’utilisateur.
Utilisateur supprimé : Le nombre de signalements ayant entraîné la suspension de l’utilisateur.
Publication supprimée : Le nombre de signalements ayant entraîné la suppression de la publication.
Publication masquée : Le nombre de signalements ayant entraîné le masquage de la publication.

Les résultats sont triés par ordre décroissant du nombre total de signalements.

Exemple de résultats

Type	Signalé	Automatisé	Total	Temps médian d’action (minutes)	Utilisateur réduit au silence	Utilisateur supprimé	Publication supprimée	Publication masquée
Spam	100	50	150	30	20	10	50	30
Inapproprié	80	5	85	45	15	5	30	20
Hors-sujet	40	2	42	25	5	0	10	15
Notifier_modérateurs	20	0	20	60	0	0	5	10
Illégal	5	1	6	120	1	1	3	2

Actions de modération entreprises

Ce rapport fournit un résumé des actions de modération entreprises dans la plage de dates spécifiée. Il agrège divers types d’actions validées, incluant le contenu signalé par les utilisateurs ou l’automatisation, les publications supprimées ou masquées, les avertissements émis, les comptes supprimés ou suspendus, et les utilisateurs réduits au silence pour des périodes prolongées. Chaque catégorie est présentée avec le nombre total de cas, offrant une vue d’ensemble de l’activité de modération.

-- [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 -- Inclure uniquement les signalements validés et pour lesquels une action a été entreprise
),
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 -- Inclure les types de signalement mappés
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- Inclure les types de signalement spécifiques pour les résultats 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
    'Contenu signalé par les utilisateurs' AS category,
    fc.user_flagged AS "Nombre de cas"
FROM flagged_content fc

UNION ALL

SELECT
    'Contenu signalé par l'automatisation' AS category,
    fc.automation_flagged AS "Nombre de cas"
FROM flagged_content fc

UNION ALL

SELECT
    'Publications supprimées pour violation des conditions' AS category,
    pdh.posts_deleted AS "Nombre de cas"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Publications masquées' AS category,
    pdh.posts_hidden AS "Nombre de cas"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Avertissements émis' AS category,
    wi.warnings_count AS "Nombre de cas"
FROM warnings_issued wi

UNION ALL

SELECT
    'Comptes supprimés' AS category,
    vs.accounts_deleted AS "Nombre de cas"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Comptes suspendus' AS category,
    vs.accounts_suspended AS "Nombre de cas"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Utilisateurs réduits au silence pendant 10+ ans' AS category,
    si.silences_count AS "Nombre de cas"
FROM silences_issued si

Paramètres utilisés

:start_date : La date de début pour filtrer les actions de modération.
:end_date : La date de fin pour filtrer les actions de modération.

Explication des résultats

La requête agrège les actions de modération en catégories et fournit le nombre total de cas pour chaque catégorie. Les catégories incluent :

Contenu signalé par les utilisateurs : Le nombre de publications signalées par les utilisateurs réguliers (excluant les signalements générés par le système).
Contenu signalé par l’automatisation : Le nombre de publications signalées par des systèmes automatisés ou des bots (par ex. spam_scanner_bot, system).
Publications supprimées pour violation des conditions : Le nombre de publications qui ont été supprimées en raison de violations des règles de la communauté ou des conditions d’utilisation.
Publications masquées : Le nombre de publications qui ont été masquées (mais pas supprimées) pour diverses raisons.
Avertissements émis : Le nombre d’avertissements émis aux utilisateurs pour comportement ou contenu inapproprié.
Comptes supprimés : Le nombre de comptes utilisateur supprimés en raison de violations, telles que le signalement en tant que spammers ou le rejet dans les files d’attente de révision.
Comptes suspendus : Le nombre de comptes utilisateur suspendus pour une période spécifique en raison de violations.
Utilisateurs réduits au silence pendant 10+ ans : Le nombre d’utilisateurs réduits au silence indéfiniment ou pour des périodes prolongées (10+ ans).

Exemple de résultats

Catégorie	Nombre de cas
Contenu signalé par les utilisateurs	150
Contenu signalé par l’automatisation	100
Publications supprimées pour violation des conditions	50
Publications masquées	30
Avertissements émis	20
Comptes supprimés	10
Comptes suspendus	15
Utilisateurs réduits au silence pendant 10+ ans	5

Actions de modération individuelles entreprises

Ce rapport fournit un journal détaillé des actions de modération individuelles validées entreprises dans la plage de dates spécifiée. Il inclut des informations sur l’utilisateur qui a effectué l’action, l’utilisateur cible, la date de l’action, la catégorie de l’action (par ex. contenu signalé, publications supprimées, avertissements émis), et le contexte ou la raison de l’action. Ce rapport est utile pour auditer des décisions de modération spécifiques et comprendre le contexte derrière chaque action.

-- [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 -- Inclure uniquement les signalements validés et pour lesquels une action a été entreprise
),
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,
        'Contenu signalé par les utilisateurs' 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,
        'Contenu signalé par l'automatisation' 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,
        'Publications supprimées pour violation des conditions' 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,
        'Publications masquées' 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,
        'Avertissements émis' 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,
        'Comptes supprimés' 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,
        'Comptes suspendus' 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,
        'Utilisateurs réduits au silence pendant 10+ ans' 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

Paramètres utilisés

:start_date : La date de début pour filtrer les actions de modération individuelles.
:end_date : La date de fin pour filtrer les actions de modération individuelles.

Explication des résultats

La requête fournit un journal détaillé des actions de modération individuelles, incluant :

Utilisateur agissant : Le nom d’utilisateur du modérateur, du système ou de l’utilisateur qui a effectué l’action.
Utilisateur cible : Le nom d’utilisateur ou l’ID de l’utilisateur qui était l’objet de l’action (par ex. l’auteur d’une publication signalée ou le destinataire d’un avertissement).
Date de l’action : La date et l’heure à laquelle l’action a eu lieu.
Catégorie : Le type d’action de modération, tel que :

Contenu signalé par les utilisateurs
Contenu signalé par l’automatisation
Publications supprimées pour violation des conditions
Publications masquées
Avertissements émis
Comptes supprimés
Comptes suspendus
Utilisateurs réduits au silence pendant 10+ ans

Contexte : Informations supplémentaires ou contenu lié à l’action, telles que le texte d’une publication signalée ou la raison d’une suspension.

Exemple de résultats

Utilisateur agissant	Utilisateur cible	Date de l’action	Catégorie	Contexte
user123	user456	2024-02-01 10:00	Contenu signalé par les utilisateurs	« Cette publication contient du contenu spam. »
spam_scanner	user789	2024-02-02 12:00	Contenu signalé par l’automatisation	« Détecté comme spam par le système. »
mod001	user456	2024-02-03 14:00	Publications supprimées pour violation des conditions	« Exemple de contenu de publication
mod002	user123	2024-02-04 16:00	Publications masquées	« Publication jugée inappropriée. »
admin001	user789	2024-02-05 18:00	Avertissements émis	NULL
admin002	user456	2024-02-06 20:00	Comptes supprimés	« Compte supprimé via la file de révision. »
mod003	user123	2024-02-07 22:00	Comptes suspendus	« Utilisateur suspendu pour spam répété. »
admin003	user789	2024-02-08 08:00	Utilisateurs réduits au silence pendant 10+ ans	« Utilisateur réduit au silence pour violations extrêmes. »

Sujet		Réponses	Vues
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	431	Décembre 22, 2023
Dashboard Report - User Flagging Ratio Data & reporting sql-query , dashboard-reports , dashboard-sql	0	358	Février 6, 2024
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	793	Octobre 30, 2024
Statistics on spam flags reported by AI triage automation Data & reporting ai , sql-query , spam	3	146	Mai 31, 2025
Show what action was taken and by who for Flags Feature	2	915	Avril 12, 2014

Analyse des rapports d'activité de modération et de signalement

Pourcentage de résolution des signalements de publication par type

Explication de la requête SQL

Paramètres utilisés

Explication des CTE (Common Table Expressions)

Explication des résultats

Exemples de résultats

Pourcentages de résolution des éléments à examiner

Explication de la requête SQL

Paramètres utilisés

Explication des CTE

Explication des résultats

Exemples de résultats

Résolutions des signalements par les modérateurs

Explication de la requête SQL

Paramètres utilisés

Explication des CTE

Explication des résultats

Exemples de résultats

Qui signale les publications

Explication de la requête SQL

Paramètres utilisés

Explication des résultats

Exemples de résultats

Notes sur les utilisateurs

Explication de la requête SQL

Paramètres utilisés

Explication des résultats

Exemples de résultats

KPI des modérateurs - Signalements et temps moyen de résolution

Explication de la requête SQL

Paramètres utilisés

Explication des CTE

Explication des résultats

Exemples de résultats

Toutes les données de signalement

Explication de la requête SQL

Paramètres utilisés

Explication des CTE

Explication des résultats

Exemples de résultats (anonymisés)

Exemples de résultats (anonymisés)

Rapports de signalement soumis

Paramètres utilisés

Explication des CTE

Explication des résultats

Exemple de résultats

Bannissements et suspensions :

Paramètres utilisés

Explication des résultats

Exemple de résultats

Actions de signalement validées

Paramètres utilisés

Explication des CTE

Explication des résultats

Exemple de résultats

Actions de modération entreprises

Paramètres utilisés

Explication des résultats

Exemple de résultats

Actions de modération individuelles entreprises

Paramètres utilisés

Explication des résultats

Exemple de résultats

Sujets connexes