이것은 관리자 활동 대시보드 보고서의 SQL 버전입니다.
이 보고서는 Discourse 사이트에서 지정된 기간 동안 관리자가 수행한 활동에 대한 포괄적인 개요를 제공합니다. 이 보고서는 관리자 활동의 여러 측면을 결합합니다: 플랫폼에서 보낸 시간, 검토된 플래그 수, 생성된 게시물, 생성된 개인 메시지(PM), 생성된 토픽, 그리고 수행된 게시물 수정(revision) 횟수.
이 대시보드 보고서는 관리 팀의 효과성과 참여도를 측정하고자 하는 관리자에게 유용한 도구로, 그들의 활동과 기여도에 대한 자세한 통찰을 제공합니다. 보고서가 제공하는 인사이트는 관리자 교육, 인정, 그리고 채용에 대한 의사결정에 활용될 수 있으며, 커뮤니티 기준을 유지하기 위해 관리 팀이 균형 잡히고 효과적인지 확인하는 데 도움을 줍니다.
--[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- 이 매개변수들은 보고서의 날짜 범위를 정의합니다. 두 날짜 매개변수 모두YYYY-MM-DD형식의 날짜를 받아들입니다.:include_admins- 이 매개변수는 관리자를 보고서에 포함할지 여부를 결정합니다.
SQL 쿼리 설명
이 보고서는 데이터 처리를 관리 가능하고 논리적인 섹션으로 나누기 위해 공용 테이블 표현식(CTE)을 사용하여 구성됩니다. 각 CTE에서 일어나는 일은 다음과 같습니다:
- mods: 관리자 상태 또는 관리자(admin) 상태(만약
:include_admins매개변수에 의해 포함된다면)를 가진 모든 사용자를 식별합니다. 후속 쿼리를 위해 관련 사용자 열만 선택합니다. - time_read: 제공된 시작일과 종료일 사이에 각 관리자가 플랫폼에서 콘텐츠를 읽는 데 보낸 총 시간(초 단위)을 계산합니다.
- flag_count: 지정된 기간 동안 관리자가 동의하거나 반대했던 플래그의 수를 계산합니다. 이는 각각의 게시물 작업 유형 ID로 표현된 여러 플래그 유형을 고려합니다.
- revision_count: 주어진 시간 범위 내에서 관리자가 다른 사용자의 게시물에 대해 수행한 게시물 수정(revision) 횟수를 계산합니다.
- topic_count: 관리자가 생성한 일반 토픽의 수를 계산합니다.
- post_count: 관리자가 일반 토픽에서 생성한 게시물의 수를 계산합니다.
- pm_count: 관리자가 시작한 개인 메시지의 수를 계산합니다.
CTE에서 데이터를 수집한 후, 메인 쿼리는 사용자 ID를 기준으로 이들을 조인하여 최종 보고서를 컴파일합니다. 이 보고서는 각 관리자의 사용자 이름, 읽는 데 보낸 총 시간(시간으로 변환됨), 검토된 플래그 수, 생성된 토픽, 생성된 개인 메시지, 생성된 게시물, 그리고 수행된 수정(revision) 횟수를 표시합니다. 결과는 관리자의 사용자 이름 순서대로 알파벳 순으로 정렬됩니다.
예시 결과
| 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 |
