Questo è un SQL del Report Dashboard per l’Attività del Moderatore.
Questo report fornisce una panoramica completa delle attività svolte dai moderatori in un determinato periodo di tempo su un sito Discourse. Il report combina molteplici aspetti dell’attività dei moderatori: tempo trascorso sulla piattaforma, numero di flag revisionati, post creati, messaggi personali (PM) creati, argomenti creati e revisioni dei post effettuate.
Questo report dashboard è uno strumento prezioso per gli amministratori che desiderano misurare l’efficacia e il coinvolgimento del loro team di moderazione, fornendo uno sguardo dettagliato sulla loro attività e sui loro contributi. Le informazioni fornite dal report possono informare le decisioni sulla formazione, il riconoscimento e il reclutamento dei moderatori, garantendo che il team di moderazione sia ben equilibrato ed efficace nel mantenere gli standard della community.
--[params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false
WITH mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
AND u.id > 0
),
time_read AS (
SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv ON m.user_id = uv.user_id
WHERE uv.visited_at >= :start_date
AND uv.visited_at <= :end_date
GROUP BY uv.user_id
),
flag_count AS (
WITH period_actions AS (
SELECT agreed_by_id,
disagreed_by_id
FROM post_actions
WHERE post_action_type_id IN (3,4,8,6,7)
AND created_at >= :start_date
AND created_at <= :end_date
),
agreed_flags AS (
SELECT pa.agreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.agreed_by_id = m.user_id
GROUP BY agreed_by_id
),
disagreed_flags AS (
SELECT pa.disagreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.disagreed_by_id = m.user_id
GROUP BY disagreed_by_id
)
SELECT
COALESCE(af.user_id, df.user_id) AS user_id,
COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
FROM agreed_flags af
FULL OUTER JOIN disagreed_flags df
ON df.user_id = af.user_id
),
revision_count AS (
SELECT pr.user_id,
COUNT(*) AS revision_count
FROM mods m
JOIN post_revisions pr
ON pr.user_id = m.user_id
JOIN posts p
ON p.id = pr.post_id
WHERE pr.created_at >= :start_date
AND pr.created_at <= :end_date
AND p.user_id <> pr.user_id
GROUP BY pr.user_id
),
topic_count AS (
SELECT t.user_id,
COUNT(*) AS topic_count
FROM mods m
JOIN topics t ON t.user_id = m.user_id
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY t.user_id
),
post_count AS (
SELECT p.user_id,
COUNT(*) AS post_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
),
pm_count AS (
SELECT p.user_id,
COUNT(*) AS pm_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
)
SELECT
m.user_id,
m.username,
fc.flag_count as flags_reviewed,
ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
tc.topic_count as topics_created,
pmc.pm_count as PMs_created,
pc.post_count as posts_created,
rc.revision_count as revisions
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
ORDER BY m.username ASC
Parametri
:start_datee:end_date- Questi parametri definiscono l’intervallo di date per il report. Entrambi i parametri di data accettano il formato dataAAAA-MM-GG.:include_admins- Questo parametro determina se includere gli amministratori nel report insieme ai moderatori.
Spiegazione della Query SQL
Il report è strutturato utilizzando espressioni di tabella comuni (CTE) per segmentare l’elaborazione dei dati in sezioni gestibili e logiche. Ecco cosa succede in ogni CTE:
- mods: Identifica tutti gli utenti con stato di moderatore o stato di amministratore (se incluso dal parametro
:include_admins). Seleziona solo le colonne utente pertinenti per le query successive. - time_read: Calcola il tempo totale (in secondi) che ciascun moderatore ha trascorso leggendo contenuti sulla piattaforma tra le date di inizio e fine fornite.
- flag_count: Conta il numero di flag che i moderatori hanno approvato o respinto durante il periodo specificato. Tiene conto di più tipi di flag rappresentati dai rispettivi ID di tipo di azione sul post.
- revision_count: Conta il numero di revisioni dei post effettuate dai moderatori sui post di altri utenti nell’intervallo di tempo specificato.
- topic_count: Conta il numero di argomenti regolari creati dai moderatori.
- post_count: Conta il numero di post creati dai moderatori in argomenti regolari.
- pm_count: Conta il numero di messaggi privati avviati dai moderatori.
Dopo aver raccolto i dati nelle CTE, la query principale li unisce in base all’ID utente e compila il report finale mostrando il nome utente di ciascun moderatore, il tempo totale trascorso a leggere (convertito in ore), il numero di flag revisionati, argomenti creati, messaggi personali creati, post creati e revisioni effettuate. I risultati sono ordinati alfabeticamente per nome utente del moderatore.
Risultati di esempio
| utente | username | flags_reviewed | time_reading_hours | topics_created | pms_created | posts_created | revisions |
|---|---|---|---|---|---|---|---|
| 1 | moderator1 | NULL | 36.11 | NULL | 344 | 8 | 15 |
| 2 | moderator2 | 46 | 104.52 | 2 | 271 | 466 | 363 |
| 3 | moderator3 | NULL | 72.15 | NULL | 418 | 64 | 16 |
