Ceci est une version SQL du rapport de tableau de bord pour l’activité des modérateurs.
Ce rapport fournit un aperçu complet des activités effectuées par les modérateurs dans un laps de temps spécifié sur un site Discourse. Le rapport combine plusieurs aspects de l’activité des modérateurs : le temps passé sur la plateforme, le nombre de drapeaux examinés, les publications créées, les messages personnels (MP) créés, les sujets créés et les révisions de publications effectuées.
Ce rapport de tableau de bord est un outil précieux pour les administrateurs cherchant à mesurer l’efficacité et l’engagement de leur équipe de modération, en offrant un aperçu détaillé de leur activité et de leurs contributions. Les informations fournies par le rapport peuvent éclairer les décisions concernant la formation, la reconnaissance et le recrutement des modérateurs, et garantir que l’équipe de modération est bien équilibrée et efficace dans le maintien des normes de la communauté.
--[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
Paramètres
:start_dateet:end_date- Ces paramètres définissent la plage de dates pour le rapport. Les deux paramètres de date acceptent le format de dateAAAA-MM-JJ.:include_admins- Ce paramètre détermine s’il faut inclure les administrateurs dans le rapport aux côtés des modérateurs.
Explication de la requête SQL
Le rapport est structuré à l’aide d’expressions de table communes (CTE) pour segmenter le traitement des données en sections gérables et logiques. Voici ce qui se passe dans chaque CTE :
- mods : Identifie tous les utilisateurs ayant le statut de modérateur ou d’administrateur (si inclus par le paramètre
:include_admins). Il ne sélectionne que les colonnes utilisateur pertinentes pour les requêtes ultérieures. - time_read : Calcule le temps total (en secondes) que chaque modérateur a passé à lire du contenu sur la plateforme entre les dates de début et de fin fournies.
- flag_count : Compte le nombre de drapeaux que les modérateurs ont approuvés ou désapprouvés pendant la période spécifiée. Il prend en compte plusieurs types de drapeaux représentés par leurs identifiants d’action de publication respectifs.
- revision_count : Compte le nombre de révisions de publications effectuées par les modérateurs sur les publications d’autres utilisateurs dans le délai imparti.
- topic_count : Compte le nombre de sujets réguliers créés par les modérateurs.
- post_count : Compte le nombre de publications créées par les modérateurs dans des sujets réguliers.
- pm_count : Compte le nombre de messages privés initiés par les modérateurs.
Après avoir collecté les données dans les CTE, la requête principale les joint sur l’ID utilisateur et compile le rapport final affichant le nom d’utilisateur de chaque modérateur, le temps total passé à lire (converti en heures), le nombre de drapeaux examinés, les sujets créés, les messages personnels créés, les publications créées et les révisions effectuées. Les résultats sont triés par ordre alphabétique par le nom d’utilisateur du modérateur.
Résultats d’exemple
| user | 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 |
