Este es una versión SQL del Informe del Panel de Actividad del Moderador.
Este informe proporciona una visión general completa de las actividades realizadas por los moderadores dentro de un período de tiempo específico en un sitio de Discourse. El informe combina múltiples aspectos de la actividad del moderador: tiempo dedicado en la plataforma, número de indicadores revisados, publicaciones creadas, mensajes personales (PM) creados, temas creados y revisiones de publicaciones realizadas.
Este informe del panel es una herramienta valiosa para los administradores que buscan medir la efectividad y el compromiso de su equipo de moderación, brindando una mirada detallada a su actividad y contribuciones. Las ideas proporcionadas por el informe pueden informar las decisiones sobre la capacitación, el reconocimiento y el reclutamiento de moderadores, y garantizar que el equipo de moderación esté bien equilibrado y sea efectivo para mantener los estándares de la comunidad.
--[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
Parámetros
:start_datey:end_date- Estos parámetros definen el rango de fechas para el informe. Ambos parámetros de fecha aceptan el formato de fechaYYYY-MM-DD.:include_admins- Este parámetro determina si se incluyen los administradores en el informe junto con los moderadores.
Explicación de la Consulta SQL
El informe está estructurado utilizando expresiones de tabla comunes (CTE) para segmentar el procesamiento de datos en secciones manejables y lógicas. Esto es lo que sucede en cada CTE:
- mods: Identifica a todos los usuarios con estado de moderador o estado de administrador (si se incluye mediante el parámetro
:include_admins). Selecciona solo las columnas de usuario relevantes para consultas posteriores. - time_read: Calcula el tiempo total (en segundos) que cada moderador ha pasado leyendo contenido en la plataforma entre las fechas de inicio y fin proporcionadas.
- flag_count: Cuenta el número de indicadores que los moderadores han aceptado o rechazado durante el período especificado. Tiene en cuenta múltiples tipos de indicadores representados por sus respectivos IDs de tipo de acción de publicación.
- revision_count: Cuenta el número de revisiones de publicaciones realizadas por moderadores en las publicaciones de otros usuarios dentro del plazo dado.
- topic_count: Cuenta el número de temas regulares creados por los moderadores.
- post_count: Cuenta el número de publicaciones creadas por los moderadores en temas regulares.
- pm_count: Cuenta el número de mensajes privados iniciados por los moderadores.
Después de recopilar los datos en las CTE, la consulta principal las une basándose en el ID de usuario y compila el informe final que muestra el nombre de usuario de cada moderador, el tiempo total dedicado a leer (convertido a horas), el número de indicadores revisados, temas creados, mensajes personales creados, publicaciones creadas y revisiones realizadas. Los resultados se ordenan alfabéticamente por el nombre de usuario del moderador.
Resultados de Ejemplo
| usuario | 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 |