Questa è una versione SQL del Report sullo stato dei flag.
Il report sullo stato dei flag è progettato per fornire informazioni sul processo di moderazione analizzando il tempo impiegato per risolvere i post segnalati entro un periodo di tempo specificato. Include dettagli sul tipo di flag sollevato, il post e gli utenti coinvolti, la data di creazione del flag, lo stato della risoluzione, il membro dello staff che ha gestito il flag e il tempo impiegato per raggiungere una risoluzione.
Questo report è utile per i community manager e i moderatori per valutare l’efficienza del team di moderazione, identificare eventuali problemi nel processo di risoluzione e garantire che gli standard della community siano mantenuti in modo tempestivo.
-- [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 -- 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
),
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
Spiegazione della query SQL
La query è strutturata utilizzando Common Table Expressions (CTE) che preparano un sottoinsieme di dati che viene poi utilizzato nell’istruzione SELECT finale per compilare il report.
Parametri
La query accetta due parametri:
:start_date: L’inizio del periodo per cui generare il report.:end_date: La fine del periodo per cui generare il report.
CTE
period_actions
Questa CTE seleziona le azioni sui post (flag) che rientrano nell’intervallo di date specificato e sono di tipi specifici (3, 4, 6, 7, 8), che corrispondono a diversi motivi di flag. Calcola il ‘time_to_resolution_minutes’ trovando la differenza tra l’ora di creazione del flag e l’ora in cui è stato risposto (accettato, rifiutato o posticipato).
poster_data
Questa CTE unisce period_actions con le tabelle posts e users per recuperare informazioni sull’autore del post segnalato, inclusi il suo ID utente, ID argomento, numero del post e nome utente.
flagger_data
Questa CTE unisce period_actions con la tabella users per ottenere informazioni sull’utente che ha segnalato il post, inclusi il suo ID utente e nome utente.
staff_data
Questa CTE recupera informazioni sul membro dello staff che ha gestito il flag unendo period_actions con la tabella users in base agli ID dei membri dello staff che hanno accettato, rifiutato o posticipato il flag.
flag_types
Questa CTE seleziona semplicemente gli ID dalla tabella post_action_types, che verranno utilizzati per determinare il tipo di flag nell’istruzione SELECT finale.
SELECT finale
L’istruzione SELECT finale combina tutte le CTE per presentare un report completo. Include le seguenti colonne:
type: Il tipo di flag sollevato, determinato dall’IDflag_type.flagged_post_id: L’ID del post segnalato.poster_user_id: L’ID dell’utente che ha pubblicato il contenuto segnalato.flagger_user_id: L’ID dell’utente che ha segnalato il contenuto.flag_created: La data in cui è stato creato il flag.resolution: Lo stato della risoluzione del flag (accettato, rifiutato, posticipato).assigned_user_id: L’ID del membro dello staff che ha gestito il flag.resolution_time (minutes): Il tempo impiegato per risolvere il flag, in minuti, arrotondato a due cifre decimali.
Il report viene quindi ordinato per data di creazione del flag (pa.created_at) in ordine crescente.
Risultati di esempio
| 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 |
| — | — | — | — | — | — | — | — |