Это SQL-версия отчёта панели мониторинга по активности модераторов.
Данный отчёт предоставляет всесторонний обзор деятельности модераторов в рамках указанного временного промежутка на сайте Discourse. Отчёт объединяет несколько аспектов работы модераторов: время, проведённое на платформе, количество рассмотренных флагов, созданных постов, личных сообщений (ЛС), созданных тем и внесённых правок в посты.
Этот отчёт панели мониторинга является ценным инструментом для администраторов, стремящихся оценить эффективность и вовлечённость своей команды модераторов, предоставляя детальную картину их активности и вклада. Инсайты, полученные из отчёта, могут помочь в принятии решений по обучению, поощрению и привлечению модераторов, а также в обеспечении сбалансированности и эффективности команды модераторов для поддержания стандартов сообщества.
--[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
Параметры
:start_dateи:end_date— эти параметры определяют диапазон дат для отчёта. Оба параметра даты принимают форматГГГГ-ММ-ДД.:include_admins— этот параметр определяет, включать ли администраторов в отчёт вместе с модераторами.
Объяснение SQL-запроса
Отчёт структурирован с использованием общих табличных выражений (CTE), чтобы разбить обработку данных на управляемые и логичные разделы. Вот что происходит в каждом CTE:
- mods: Определяет всех пользователей со статусом модератора или администратора (если включено параметром
:include_admins). Выбирает только соответствующие столбцы пользователей для дальнейших запросов. - time_read: Вычисляет общее время (в секундах), которое каждый модератор потратил на чтение контента на платформе между указанными датами начала и конца.
- flag_count: Подсчитывает количество флагов, с которыми модераторы согласились или не согласились в течение указанного периода. Учитываются несколько типов флагов, представленных их соответствующими идентификаторами типов действий над постом.
- revision_count: Подсчитывает количество правок в постах, внесённых модераторами в чужие посты в течение заданного временного промежутка.
- topic_count: Подсчитывает количество обычных тем, созданных модераторами.
- post_count: Подсчитывает количество постов, созданных модераторами в обычных темах.
- pm_count: Подсчитывает количество личных сообщений, инициированных модераторами.
После сбора данных в CTE основной запрос соединяет их по идентификатору пользователя и формирует итоговый отчёт, отображающий имя пользователя каждого модератора, общее время, проведённое за чтением (преобразованное в часы), количество рассмотренных флагов, созданных тем, личных сообщений, постов и внесённых правок. Результаты сортируются в алфавитном порядке по имени пользователя модератора.
Пример результатов
| 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 |
