Ceci est une version SQL du rapport sur l’état des drapeaux.
Le rapport sur l’état des drapeaux est conçu pour fournir des informations sur le processus de modération en analysant le temps nécessaire pour résoudre les publications signalées sur une période donnée. Il comprend des détails sur le type de signalement, les publications et les utilisateurs concernés, la date de création du signalement, le statut de résolution, le membre du personnel qui a traité le signalement et le temps nécessaire pour parvenir à une résolution.
Ce rapport est utile aux responsables de communauté et aux modérateurs pour évaluer l’efficacité de l’équipe de modération, identifier tout problème dans le processus de résolution et garantir que les normes de la communauté sont respectées en temps opportun.
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-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 -- time to resolution in 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
),
poster_data AS (
SELECT pa.id,
p.user_id AS poster_id,
p.topic_id,
p.post_number,
u.username_lower AS poster_username,
u.uploaded_avatar_id AS poster_avatar_id
FROM period_actions pa
JOIN posts p ON p.id = pa.post_id
JOIN users u ON u.id = p.user_id
),
flagger_data AS (
SELECT pa.id,
u.id AS flagger_id,
u.username_lower AS flagger_username,
u.uploaded_avatar_id AS flagger_avatar_id
FROM period_actions pa
JOIN users u ON u.id = pa.user_id
),
staff_data AS (
SELECT pa.id,
u.id AS staff_id,
u.username_lower AS staff_username,
u.uploaded_avatar_id AS staff_avatar_id
FROM period_actions pa
JOIN users u ON u.id = COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id)
),
flag_types AS (
SELECT pat.id,
pat.id AS flag_type
FROM post_action_types pat
)
SELECT
CASE
WHEN pat.flag_type = 3 THEN 'off_topic'
WHEN pat.flag_type = 4 THEN 'inappropriate'
WHEN pat.flag_type = 6 THEN 'notify_user'
WHEN pat.flag_type = 7 THEN 'notify_moderators'
WHEN pat.flag_type = 8 THEN 'spam'
END "type",
pa.post_id as flagged_post_id,
pd.poster_id as poster_user_id,
fd.flagger_id as flagger_user_id,
DATE(pa.created_at) as flag_created,
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'
END "resolution",
sd.staff_id as assigned_user_id,
ROUND(pa.time_to_resolution_minutes,2) as "resolution_time (minutes)"
FROM period_actions pa
JOIN poster_data pd ON pd.id = pa.id
JOIN flagger_data fd ON fd.id = pa.id
LEFT JOIN staff_data sd ON sd.id = pa.id
JOIN flag_types pat ON pat.id = pa.post_action_type_id
ORDER BY pa.created_at ASC
Explication de la requête SQL
La requête est structurée à l’aide d’expressions de table communes (CTE) qui préparent un sous-ensemble de données utilisé ensuite dans l’instruction SELECT finale pour compiler le rapport.
Paramètres
La requête accepte deux paramètres :
:start_date: Le début de la période pour laquelle générer le rapport.:end_date: La fin de la période pour laquelle générer le rapport.
CTE
period_actions
Cette CTE sélectionne les actions sur les publications (signalements) qui se situent dans la plage de dates spécifiée et qui sont de types spécifiques (3, 4, 6, 7, 8), correspondant à différentes raisons de signalement. Elle calcule le « time_to_resolution_minutes » en calculant la différence entre l’heure de création du signalement et l’heure à laquelle une réponse a été apportée (accepté, rejeté ou différé).
poster_data
Cette CTE joint period_actions aux tables posts et users pour récupérer des informations sur l’auteur de la publication signalée, y compris son ID utilisateur, son ID de sujet, son numéro de publication et son nom d’utilisateur.
flagger_data
Cette CTE joint period_actions à la table users pour obtenir des informations sur l’utilisateur qui a signalé la publication, y compris son ID utilisateur et son nom d’utilisateur.
staff_data
Cette CTE récupère des informations sur le membre du personnel qui a traité le signalement en joignant period_actions à la table users en fonction des ID des membres du personnel qui ont accepté, rejeté ou différé le signalement.
flag_types
Cette CTE sélectionne simplement les ID de la table post_action_types, qui seront utilisés pour déterminer le type de signalement dans l’instruction SELECT finale.
SELECT final
L’instruction SELECT finale combine toutes les CTE pour présenter un rapport complet. Elle comprend les colonnes suivantes :
type: Le type de signalement émis, déterminé par l’IDflag_type.flagged_post_id: L’ID de la publication signalée.poster_user_id: L’ID de l’utilisateur qui a publié le contenu signalé.flagger_user_id: L’ID de l’utilisateur qui a signalé le contenu.flag_created: La date à laquelle le signalement a été créé.resolution: Le statut de résolution du signalement (accepté, rejeté, différé).assigned_user_id: L’ID du membre du personnel qui a traité le signalement.resolution_time (minutes): Le temps nécessaire pour résoudre le signalement, en minutes, arrondi à deux décimales.
Le rapport est ensuite trié par date de création du signalement (pa.created_at) par ordre croissant.
Résultats d’exemple
| type | flagged_post | poster_user | flagger_user | flag_created | resolution | assigned_user | resolution_time (minutes) |
|---|---|---|---|---|---|---|---|
| off_topic | Example_Post_Link1 | example_user1 | example_user2 | 2023-01-01 | agreed | example_user3 | 4.56 |
| inappropriate | Example_Post_Link2 | example_user1 | example_user2 | 2023-01-02 | disagreed | example_user3 | 38.76 |
| spam | Example_Post_Link3 | example_user1 | example_user2 | 2023-01-03 | deferred | example_user3 | 79.32 |
| — | — | — | — | — | — | — | — |