Este é um relatório de painel em versão SQL para atividade de moderador.
Este relatório fornece uma visão geral abrangente das atividades realizadas pelos moderadores dentro de um período de tempo especificado em um site Discourse. O relatório combina múltiplos aspectos da atividade do moderador: tempo gasto na plataforma, número de sinalizações revisadas, postagens criadas, mensagens pessoais (PMs) criadas, tópicos criados e revisões de postagens feitas.
Este relatório de painel é uma ferramenta valiosa para administradores que buscam medir a eficácia e o engajamento de sua equipe de moderação, fornecendo uma visão detalhada de suas atividades e contribuições. As informações fornecidas pelo relatório podem informar decisões sobre treinamento, reconhecimento e recrutamento de moderadores, garantindo que a equipe de moderação seja equilibrada e eficaz na manutenção dos padrões da comunidade.
--[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_datee:end_date- Estes parâmetros definem o intervalo de datas para o relatório. Ambos os parâmetros de data aceitam o formato de dataAAAA-MM-DD.:include_admins- Este parâmetro determina se os administradores devem ser incluídos no relatório juntamente com os moderadores.
Explicação da Consulta SQL
O relatório é estruturado usando expressões de tabela comuns (CTEs) para segmentar o processamento de dados em seções gerenciáveis e lógicas. Veja o que acontece em cada CTE:
- mods: Identifica todos os usuários com status de moderador ou status de administrador (se incluído pelo parâmetro
:include_admins). Ele seleciona apenas as colunas de usuário relevantes para consultas posteriores. - time_read: Calcula o tempo total (em segundos) que cada moderador passou lendo conteúdo na plataforma entre as datas de início e fim fornecidas.
- flag_count: Conta o número de sinalizações que os moderadores concordaram ou discordaram durante o período especificado. Leva em consideração múltiplos tipos de sinalização representados por seus respectivos IDs de tipo de ação de postagem.
- revision_count: Conta o número de revisões de postagens feitas por moderadores em postagens de outros usuários dentro do período de tempo fornecido.
- topic_count: Conta o número de tópicos regulares criados por moderadores.
- post_count: Conta o número de postagens criadas por moderadores em tópicos regulares.
- pm_count: Conta o número de mensagens privadas iniciadas por moderadores.
Após coletar os dados nas CTEs, a consulta principal as une com base no ID do usuário e compila o relatório final exibindo o nome de usuário de cada moderador, o tempo total gasto lendo (convertido para horas), o número de sinalizações revisadas, tópicos criados, mensagens pessoais criadas, postagens criadas e revisões feitas. Os resultados são ordenados alfabeticamente pelo nome de usuário do moderador.
Resultados de Exemplo
| 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 |
