이 가이드에는 관리 관련 활동을 분석하는 데 도움이 되도록 설계된 Discourse용 다양한 SQL 리포트를 포함하고 있습니다.
이 주제에서는 다음에 대한 자세한 Data Explorer 쿼리를 찾을 수 있습니다:
- 플래그 해결 통계.
- 검토 대상(Reviewable) 해결 비율.
- 관리자별 성과 지표.
- 사용자 플래깅 활동에 대한 인사이트.
- 모든 플래그된 사용자, 게시물, 주제 작업에 대한 종합 데이터
유형별 게시물 플래그 해결 비율
SQL 쿼리 설명
이 쿼리는 플래그된 게시물의 해결 상태(동의, 반의, 보류, 삭제)를 플래그 유형별로 그룹화하여 비율을 계산합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
WITH period_actions AS (
SELECT pa.id,
pa.post_action_type_id,
pa.created_at,
pa.agreed_at,
pa.disagreed_at,
pa.deferred_at,
pa.agreed_by_id,
pa.disagreed_by_id,
pa.deferred_by_id,
pa.deleted_at,
pa.post_id,
pa.user_id,
COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 해결까지 소요된 시간(분)
FROM post_actions pa
WHERE pa.post_action_type_id IN (3,4,6,7,8)
AND pa.created_at >= :start_date
AND pa.created_at <= :end_date
),
flag_types AS (
SELECT pat.id,
CASE
WHEN pat.id = 3 THEN 'off_topic'
WHEN pat.id = 4 THEN 'inappropriate'
WHEN pat.id = 6 THEN 'notify_user'
WHEN pat.id = 7 THEN 'notify_moderators'
WHEN pat.id = 8 THEN 'spam'
END AS flag_type
FROM post_action_types pat
),
flag_resolutions AS (
SELECT
pa.post_action_type_id,
CASE
WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
END AS resolution,
COUNT(*) AS resolution_count
FROM period_actions pa
GROUP BY pa.post_action_type_id, resolution
),
flag_totals AS (
SELECT
pa.post_action_type_id,
COUNT(*) AS total_flags
FROM period_actions pa
GROUP BY pa.post_action_type_id
),
resolution_percentages AS (
SELECT
fty.flag_type,
fr.resolution,
fr.resolution_count,
ft.total_flags,
ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
FROM flag_resolutions fr
JOIN flag_totals ft ON ft.post_action_type_id = fr.post_action_type_id
JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
SELECT
flag_type,
MAX(CASE WHEN resolution = 'agreed' THEN resolution_percentage ELSE 0 END) AS agreed_percentage,
MAX(CASE WHEN resolution = 'disagreed' THEN resolution_percentage ELSE 0 END) AS disagreed_percentage,
MAX(CASE WHEN resolution = 'deferred' THEN resolution_percentage ELSE 0 END) AS deferred_percentage,
MAX(CASE WHEN resolution = 'deleted' THEN resolution_percentage ELSE 0 END) AS deleted_percentage,
MAX(CASE WHEN resolution = 'agreed' THEN resolution_count ELSE 0 END) AS agreed_count,
MAX(CASE WHEN resolution = 'disagreed' THEN resolution_count ELSE 0 END) AS disagreed_count,
MAX(CASE WHEN resolution = 'deferred' THEN resolution_count ELSE 0 END) AS deferred_count,
MAX(CASE WHEN resolution = 'deleted' THEN resolution_count ELSE 0 END) AS deleted_count,
MAX(total_flags) AS total_flags
FROM resolution_percentages
GROUP BY flag_type
)
SELECT
flag_type,
agreed_percentage,
agreed_count,
disagreed_percentage,
disagreed_count,
deferred_percentage,
deferred_count,
deleted_percentage,
deleted_count,
total_flags
FROM pivoted_data
ORDER BY flag_type
사용된 파라미터
:start_date: 플래그된 게시물을 필터링하기 위한 시작 날짜.:end_date: 플래그된 게시물을 필터링하기 위한 종료 날짜.
CTE 설명
period_actions: 지정된 날짜 범위 내의 플래그된 게시물을 필터링하고 각 플래그에 대한 해결까지의 소요 시간을 계산합니다.flag_types: 플래그 유형 ID를 사람이 읽을 수 있는 이름(예: 오프토픽, 부적절, 스팸)으로 매핑합니다.flag_resolutions: 플래그를 유형과 해결 상태(동의, 반의, 보류, 삭제)별로 그룹화하고 각 해결 상태의 발생 횟수를 계산합니다.flag_totals: 각 플래그 유형에 대한 총 플래그 수를 계산합니다.resolution_percentages: 해결 상태별 카운트와 총 플래그 수를 결합하여 각 플래그 유형별 해결 상태별 비율을 계산합니다.pivoted_data: 데이터를 피벗하여 각 해결 상태별 비율과 카운트를 별도의 열로 표시합니다.
결과 설명
최종 결과는 다음을 표시하는 테이블입니다:
- 플래그 유형(예: 오프토픽, 스팸).
- 각 해결 상태(동의, 반의, 보류, 삭제)에 대한 비율과 카운트.
- 각 플래그 유형별 총 플래그 수.
결과 예시
| 플래그 유형 | 동의 비율(%) | 동의 카운트 | 반의 비율(%) | 반의 카운트 | 보류 비율(%) | 보류 카운트 | 삭제 비율(%) | 삭제 카운트 | 총 플래그 수 |
|---|---|---|---|---|---|---|---|---|---|
| off_topic | 50.00 | 25 | 30.00 | 15 | 10.00 | 5 | 10.00 | 5 | 50 |
| spam | 70.00 | 35 | 20.00 | 10 | 5.00 | 2 | 5.00 | 3 | 50 |
검토 대상(Reviewables) 해결 비율
SQL 쿼리 설명
이 쿼리는 지정된 날짜 범위 내의 검토 대상 항목(예: 플래그된 게시물)의 해결 상태를 분석합니다. 각 플래그 유형별 해결 상태의 비율과 카운트를 계산합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
WITH flag_data AS (
SELECT
r.type AS flag_type,
r.status AS resolution_status,
COUNT(*) AS flag_count
FROM
reviewables r
WHERE
r.created_at BETWEEN :start_date AND :end_date
GROUP BY
r.type,
r.status
),
flag_totals AS (
SELECT
flag_type,
SUM(flag_count) AS total_flags
FROM
flag_data
GROUP BY
flag_type
),
flag_percentages AS (
SELECT
fd.flag_type,
fd.resolution_status,
fd.flag_count,
ROUND((fd.flag_count::decimal / ft.total_flags) * 100, 2) AS percentage
FROM
flag_data fd
JOIN
flag_totals ft
ON
fd.flag_type = ft.flag_type
)
SELECT
fp.flag_type,
-- 비율
COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.percentage END), 0) AS pending_percentage,
COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.percentage END), 0) AS approved_percentage,
COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.percentage END), 0) AS rejected_percentage,
COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.percentage END), 0) AS ignored_percentage,
COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.percentage END), 0) AS deleted_percentage,
-- 카운트
COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.flag_count END), 0) AS pending_count,
COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.flag_count END), 0) AS approved_count,
COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.flag_count END), 0) AS rejected_count,
COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.flag_count END), 0) AS ignored_count,
COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.flag_count END), 0) AS deleted_count
FROM
flag_percentages fp
GROUP BY
fp.flag_type
ORDER BY
fp.flag_type
사용된 파라미터
:start_date: 검토 대상을 필터링하기 위한 시작 날짜.:end_date: 검토 대상을 필터링하기 위한 종료 날짜.
CTE 설명
flag_data: 검토 대상을 플래그 유형과 해결 상태로 그룹화하고 각 조합의 발생 횟수를 계산합니다.flag_totals: 각 플래그 유형에 대한 총 플래그 수를 계산합니다.flag_percentages: 플래그 카운트와 총합을 결합하여 각 플래그 유형별 해결 상태의 비율을 계산합니다.
결과 설명
최종 결과는 다음을 표시하는 테이블입니다:
- 플래그 유형.
- 각 해결 상태(대기 중, 승인됨, 거부됨, 무시됨, 삭제됨)에 대한 비율과 카운트.
결과 예시
| 플래그 유형 | 대기 중 비율(%) | 대기 중 카운트 | 승인 비율(%) | 승인 카운트 | 거부 비율(%) | 거부 카운트 | 무시 비율(%) | 무시 카운트 | 삭제 비율(%) | 삭제 카운트 |
|---|---|---|---|---|---|---|---|---|---|---|
| off_topic | 20.00 | 10 | 50.00 | 25 | 10.00 | 5 | 10.00 | 5 | 10.00 | 5 |
| spam | 10.00 | 5 | 70.00 | 35 | 10.00 | 5 | 5.00 | 2 | 5.00 | 3 |
관리자 플래그 해결 현황
SQL 쿼리 설명
이 쿼리는 어떤 관리자가 플래그된 게시물을 해결했는지, 어떤 유형의 플래그를 처리했으며, 어떤 해결 조치를 적용했는지에 대한 인사이트를 제공합니다. 각 해결 유형별 비율과 카운트를 계산합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false
WITH period_actions AS (
SELECT
pa.id,
pa.post_action_type_id,
pa.created_at,
pa.agreed_at,
pa.disagreed_at,
pa.deferred_at,
pa.agreed_by_id,
pa.disagreed_by_id,
pa.deferred_by_id,
pa.deleted_at,
pa.post_id,
pa.user_id,
COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 해결까지 소요된 시간(분)
FROM post_actions pa
WHERE pa.post_action_type_id IN (3,4,6,7,8)
AND pa.created_at >= :start_date
AND pa.created_at <= :end_date
),
flag_types AS (
SELECT
pat.id,
CASE
WHEN pat.id = 3 THEN 'off_topic'
WHEN pat.id = 4 THEN 'inappropriate'
WHEN pat.id = 6 THEN 'notify_user'
WHEN pat.id = 7 THEN 'notify_moderators'
WHEN pat.id = 8 THEN 'spam'
END AS flag_type
FROM post_action_types pat
),
flag_resolutions AS (
SELECT
pa.user_id,
pa.post_action_type_id,
CASE
WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
END AS resolution,
COUNT(*) AS resolution_count
FROM period_actions pa
GROUP BY pa.user_id, pa.post_action_type_id, resolution
),
flag_totals AS (
SELECT
pa.user_id,
pa.post_action_type_id,
COUNT(*) AS total_flags
FROM period_actions pa
GROUP BY pa.user_id, pa.post_action_type_id
),
resolution_percentages AS (
SELECT
fr.user_id,
fty.flag_type,
fr.resolution,
fr.resolution_count,
ft.total_flags,
ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
FROM flag_resolutions fr
JOIN flag_totals ft ON ft.user_id = fr.user_id AND ft.post_action_type_id = fr.post_action_type_id
JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
SELECT
rp.user_id,
rp.flag_type,
MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_percentage ELSE 0 END) AS agreed_percentage,
MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_percentage ELSE 0 END) AS disagreed_percentage,
MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_percentage ELSE 0 END) AS deferred_percentage,
MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_percentage ELSE 0 END) AS deleted_percentage,
MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_count ELSE 0 END) AS agreed_count,
MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_count ELSE 0 END) AS disagreed_count,
MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_count ELSE 0 END) AS deferred_count,
MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_count ELSE 0 END) AS deleted_count,
MAX(rp.total_flags) AS total_flags
FROM resolution_percentages rp
GROUP BY rp.user_id, rp.flag_type
)
SELECT
u.id AS user_id,
u.username,
p.flag_type,
p.agreed_percentage,
p.agreed_count,
p.disagreed_percentage,
p.disagreed_count,
p.deferred_percentage,
p.deferred_count,
p.deleted_percentage,
p.deleted_count,
p.total_flags
FROM pivoted_data p
JOIN users u ON u.id = p.user_id
WHERE (:only_staff = false OR (u.admin = true OR u.moderator = true))
ORDER BY u.username, p.flag_type, p.total_flags
사용된 파라미터
:start_date: 플래그된 게시물을 필터링하기 위한 시작 날짜.:end_date: 플래그된 게시물을 필터링하기 위한 종료 날짜.:only_staff: 결과에 스태프 멤버만 포함하도록 필터링하기 위한 불리언 파라미터.
CTE 설명
period_actions: 지정된 날짜 범위 내의 플래그된 게시물을 필터링하고 각 플래그에 대한 해결까지의 소요 시간을 계산합니다.flag_types: 플래그 유형 ID를 사람이 읽을 수 있는 이름으로 매핑합니다.flag_resolutions: 플래그를 사용자, 플래그 유형, 해결 상태로 그룹화하고 각 조합의 발생 횟수를 계산합니다.flag_totals: 각 사용자와 플래그 유형에 대한 총 플래그 수를 계산합니다.resolution_percentages: 해결 상태별 카운트와 총합을 결합하여 각 해결 유형별 비율을 계산합니다.pivoted_data: 데이터를 피벗하여 각 해결 상태별 비율과 카운트를 별도의 열로 표시합니다.
결과 설명
최종 결과는 다음을 표시하는 테이블입니다:
- 관리자 사용자 이름.
- 각 해결 상태(동의, 반의, 보류, 삭제)에 대한 비율과 카운트.
- 각 관리자가 처리한 총 플래그 수.
결과 예시
| 관리자 | 플래그 유형 | 동의 비율(%) | 동의 카운트 | 반의 비율(%) | 반의 카운트 | 보류 비율(%) | 보류 카운트 | 삭제 비율(%) | 삭제 카운트 | 총 플래그 수 |
|---|---|---|---|---|---|---|---|---|---|---|
| mod1 | off_topic | 60.00 | 30 | 20.00 | 10 | 10.00 | 5 | 10.00 | 5 | 50 |
| mod2 | spam | 70.00 | 35 | 20.00 | 10 | 5.00 | 2 | 5.00 | 3 | 50 |
게시물 플래깅 사용자
SQL 쿼리 설명
이 쿼리는 지정된 날짜 범위 내에서 게시물을 플래그한 사용자를 식별하고 각 사용자가 제출한 총 플래그 수를 계산합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false
SELECT
u.id AS user_id,
u.username,
COUNT(pa.id) AS flag_count
FROM post_actions pa
JOIN users u ON u.id = pa.user_id
WHERE pa.post_action_type_id IN (3, 4, 6, 7, 8) -- 플래그 유형
AND pa.created_at >= :start_date
AND pa.created_at <= :end_date
AND (:only_staff = false OR (u.admin = true OR u.moderator = true))
GROUP BY u.id, u.username
ORDER BY flag_count DESC, u.username
LIMIT 10
사용된 파라미터
:start_date: 플래그된 게시물을 필터링하기 위한 시작 날짜.:end_date: 플래그된 게시물을 필터링하기 위한 종료 날짜.:only_staff: 결과에 스태프 멤버만 포함하도록 필터링하기 위한 불리언 파라미터.
결과 설명
최종 결과는 플래그 카운트가 있는 사용자의 순위 목록입니다.
결과 예시
| 사용자 ID | 사용자 이름 | 플래그 카운트 |
|---|---|---|
| 1 | user1 | 50 |
| 2 | user2 | 30 |
| 3 | user3 | 20 |
사용자 노트
SQL 쿼리 설명
이 쿼리는 plugin_store_rows 테이블에 저장된 사용자 노트를 검색합니다. 사용자 ID, 생성 날짜, 노트 내용, 생성자 ID와 같은 세부 정보를 추출합니다.
-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-01-01
WITH user_notes AS (
SELECT
REPLACE(key, 'notes:', '')::int AS user_id,
notes.value->>'created_at' AS created_at,
notes.value->>'raw' AS user_note,
notes.value->>'created_by' AS created_by
FROM plugin_store_rows,
LATERAL json_array_elements(value::json) notes
WHERE plugin_name = 'user_notes'
ORDER BY 2 DESC
)
SELECT
un.user_id,
un.created_at::date,
un.user_note,
un.created_by AS created_by_user_id
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at::date BETWEEN :start_date AND :end_date
ORDER BY created_at DESC
사용된 파라미터
:start_date: 사용자 노트를 필터링하기 위한 시작 날짜.:end_date: 사용자 노트를 필터링하기 위한 종료 날짜.
결과 설명
최종 결과는 관련 메타데이터가 포함된 사용자 노트의 상세 목록입니다.
결과 예시
| 사용자 ID | 생성일 | 사용자 노트 | 생성한 사용자 ID |
|---|---|---|---|
| 1 | 2025-01-01 | 이 사용자는 도움이 됩니다. | 2 |
| 2 | 2025-02-01 | 이 사용자는 다른 두 사용자 계정과 관련이 있습니다. | 3 |
관리자 KPI - 플래그 및 평균 플래그 해결 시간
SQL 쿼리 설명
이 쿼리는 각 관리자가 처리한 플래그 수와 평균 해결 시간(분)을 계산하여 관리자 성과를 평가합니다.
-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-01-01
WITH period_actions AS (
SELECT pa.id,
pa.post_action_type_id,
pa.created_at,
pa.agreed_at,
pa.disagreed_at,
pa.deferred_at,
pa.agreed_by_id,
pa.disagreed_by_id,
pa.deferred_by_id,
pa.post_id,
pa.user_id,
COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 해결까지 소요된 시간(분)
FROM post_actions pa
WHERE pa.post_action_type_id IN (3,4,6,7,8) -- 플래그 유형
AND pa.created_at >= :start_date
AND pa.created_at <= :end_date
),
moderator_actions AS (
SELECT pa.id,
pa.post_id,
pa.created_at,
pa.responded_at,
pa.time_to_resolution_minutes,
COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) AS moderator_id
FROM period_actions pa
WHERE COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) IS NOT NULL
),
moderator_stats AS (
SELECT
m.moderator_id,
u.username AS moderator_username,
COUNT(m.id) AS handled_flags,
AVG(m.time_to_resolution_minutes) AS avg_resolution_time_minutes
FROM moderator_actions m
JOIN users u ON u.id = m.moderator_id
GROUP BY m.moderator_id, u.username
)
SELECT
ms.moderator_username,
ms.handled_flags,
ROUND(ms.avg_resolution_time_minutes::numeric, 2) AS avg_resolution_time_minutes
FROM moderator_stats ms
ORDER BY ms.handled_flags DESC, ms.avg_resolution_time_minutes ASC
사용된 파라미터
:start_date: 플래그된 게시물을 필터링하기 위한 시작 날짜.:end_date: 플래그된 게시물을 필터링하기 위한 종료 날짜.
CTE 설명
period_actions: 지정된 날짜 범위 내의 플래그된 게시물을 필터링하고 각 플래그에 대한 해결까지의 소요 시간을 계산합니다.moderator_actions: 관리자가 해결한 플래그를 식별하고 각 플래그에 대한 해결까지의 소요 시간을 계산합니다.moderator_stats: 플래그를 관리자로 그룹화하고 처리한 총 플래그 수와 평균 해결 시간을 계산합니다.
결과 설명
최종 결과는 처리한 플래그 카운트와 평균 해결 시간이 있는 관리자의 순위 목록입니다.
결과 예시
| 관리자 사용자 이름 | 처리한 플래그 수 | 평균 해결 시간(분) |
|---|---|---|
| mod1 | 50 | 15.00 |
| mod2 | 30 | 20.00 |
모든 플래그 데이터
SQL 쿼리 설명
이 쿼리는 지정된 날짜 범위 내의 모든 플래그된 사용자, 게시물, 주제 데이터에 대한 종합 데이터셋을 제공합니다. 플래그 유형, 플래그된 항목, 플래그 이유, 플래그 출처, 해결 결정, 관련 메시지 등의 세부 정보를 포함하기 위해 여러 테이블의 데이터를 결합합니다.
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01
WITH flag_data AS (
SELECT
r.id AS flag_id,
p.id AS post_id,
p.topic_id,
p.raw AS flagged_item_text,
p.user_id AS post_author_id,
fu.username AS flagged_by_username,
r.created_at AS flagged_date,
r.type AS flag_type,
r.reviewable_by_moderator AS flag_source,
r.payload AS flag_reason,
r.status AS review_status,
r.potentially_illegal AS potentially_illegal,
rs.reviewed_by_id,
rs.reviewed_at,
rs.score AS review_score,
ru.username AS reviewed_by_username,
p.deleted_at AS post_deleted_at,
p.hidden_at AS post_hidden_at,
u.silenced_till AS user_silenced_till,
u.suspended_till AS user_suspended_till
FROM
reviewables r
LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
LEFT JOIN users fu ON r.created_by_id = fu.id
LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
LEFT JOIN users u ON p.user_id = u.id
WHERE
r.created_at BETWEEN :start_date AND :end_date
--AND r.status = 1 -- 동의하고 조치가 취해진 플래그만 포함
),
review_decisions AS (
SELECT
0 AS status_code, 'pending' AS decision_name
UNION ALL
SELECT
1 AS status_code, 'agreed' AS decision_name
UNION ALL
SELECT
2 AS status_code, 'disagreed' AS decision_name
UNION ALL
SELECT
3 AS status_code, 'ignored' AS decision_name
),
flag_types AS (
SELECT
3 AS post_action_type_id, 'off_topic' AS flag_type_name
UNION ALL
SELECT
4 AS post_action_type_id, 'inappropriate' AS flag_type_name
UNION ALL
SELECT
6 AS post_action_type_id, 'notify_user' AS flag_type_name
UNION ALL
SELECT
7 AS post_action_type_id, 'notify_moderators' AS flag_type_name
UNION ALL
SELECT
8 AS post_action_type_id, 'spam' AS flag_type_name
UNION ALL
SELECT
10 AS post_action_type_id, 'illegal' AS flag_type_name
)
SELECT
fd.flag_id,
fd.post_id AS flagged_item,
fd.flagged_by_username,
fd.flagged_date,
fd.flag_type,
ft.flag_type_name AS flag_type_name,
fd.flag_source AS reviewable_by_moderator,
fd.flag_reason,
fd.flagged_item_text,
pa.related_post_id AS related_message_id_post_id,
regexp_replace(rp.raw, '(https?://[^\s]+)', '', 'g') AS related_message_text, -- URL만 제거
fd.reviewed_at,
fd.reviewed_by_username AS reviewed_by,
rd.decision_name AS review_decision,
CASE
WHEN fd.user_silenced_till IS NOT NULL THEN 'User silenced'
WHEN fd.user_suspended_till IS NOT NULL THEN 'User suspended'
WHEN fd.post_deleted_at IS NOT NULL THEN 'Post deleted'
WHEN fd.post_hidden_at IS NOT NULL THEN 'Post hidden'
ELSE 'No action taken'
END AS action_taken,
CASE
WHEN fd.reviewed_at IS NOT NULL THEN ROUND(EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date)) / 60, 2)
ELSE NULL
END AS review_time_minutes -- 분 단위 시간 차이
FROM
flag_data fd
LEFT JOIN post_actions pa
ON pa.post_id = fd.post_id
LEFT JOIN posts rp
ON pa.related_post_id = rp.id
LEFT JOIN flag_types ft
ON pa.post_action_type_id = ft.post_action_type_id
LEFT JOIN review_decisions rd
ON fd.review_status = rd.status_code
ORDER BY
fd.flagged_date DESC
사용된 파라미터
:start_date: 플래그된 게시물을 필터링하기 위한 시작 날짜.:end_date: 플래그된 게시물을 필터링하기 위한 종료 날짜.
CTE 설명
flag_data: 플래그 ID, 플래그된 항목, 플래그 유형, 플래그 이유, 플래그 출처, 검토 세부 정보 등 플래그된 게시물의 상세 정보를 검색합니다.review_decisions: 검토 상태 코드를 사람이 읽을 수 있는 결정 이름(예: 대기 중, 동의, 반의, 무시)으로 매핑합니다.flag_types: 게시물 작업 유형 ID를 사람이 읽을 수 있는 플래그 유형 이름(예: 오프토픽, 부적절, 스팸)으로 매핑합니다.
결과 설명
- 플래그 ID: 플래그의 고유 식별자.
- 플래그된 항목: 플래그된 게시물의 ID.
- 플래그한 사용자 이름: 게시물을 플래그한 사용자의 사용자 이름.
- 플래그 날짜: 플래그가 생성된 날짜.
- 플래그 유형: 플래그의 숫자 유형.
- 플래그 유형 이름: 플래그 유형의 사람이 읽을 수 있는 이름(예: 오프토픽, 스팸).
- 관리자 검토 가능 여부: 플래그가 사용자에 의해 제기되었는지 시스템에 의해 제기되었는지를 나타냅니다.
- 플래그 이유: 플래그에 제공된 이유.
- 플래그된 항목 텍스트: 플래그된 게시물의 내용.
- 관련 메시지 ID: 관련 메시지의 ID(해당하는 경우).
- 관련 메시지 텍스트: 명확성을 위해 URL이 제거된 관련 메시지의 내용.
- 검토한 사용자 이름: 플래그를 검토한 관리자의 사용자 이름.
- 검토 결정: 검토자가 내린 결정(예: 동의, 반의, 무시, 삭제).
- 취해진 조치: 플래그 결과로 취해진 조치(예: 사용자 무음 또는 정지, 게시물 삭제 또는 숨김, 조치 없음).
- 검토 시간(분): 플래그를 검토하는 데 걸린 시간(분). 플래그 생성 시간과 검토 시간의 차이로 계산됩니다.
결과 예시 (익명화됨)
결과 예시 (익명화됨)
| 플래그 ID | 플래그된 항목 | 플래그한 사용자 이름 | 플래그 날짜 | 플래그 유형 | 플래그 유형 이름 | 관리자 검토 가능 여부 | 플래그 이유 | 플래그된 항목 텍스트 | 관련 메시지 ID | 관련 메시지 텍스트 | 검토한 사용자 이름 | 검토 결정 | 취해진 조치 | 검토 시간(분) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 12345 | 67890 | user123 | 2025-04-01 12:00 | 8 | Spam | true | Spam content | “Buy now at spam.com” | 98765 | “Check this out!” | mod456 | Agreed | Post deleted | 15.25 |
| 12346 | 67891 | user124 | 2025-04-02 14:30 | 4 | Inappropriate | false | Offensive | “This is inappropriate!” | NULL | NULL | mod457 | Disagreed | No action taken | 30.50 |
제출된 플래그 보고서
이 보고서는 지정된 날짜 범위 내에서 제출된 플래그의 개요를 제공합니다. 플래그를 유형(예: 스팸, 부적절)별로 분류하고, 사용자 보고 플래그와 시스템 생성 플래그를 구별합니다. 각 유형별 총 플래그 수를 포함하여 플랫폼에서 가장 흔히 플래그되는 문제를 식별하는 데 도움이 됩니다.
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01
WITH flag_data AS (
SELECT
r.id AS flag_id,
p.id AS post_id,
p.topic_id,
p.raw AS flagged_item_text,
p.user_id AS post_author_id,
fu.username AS flagged_by_username,
r.created_at AS flagged_date,
r.type AS flag_type,
r.reviewable_by_moderator AS flag_source,
r.payload AS flag_reason,
r.status AS review_status,
rs.reviewed_by_id,
rs.reviewed_at,
rs.score AS review_score,
ru.username AS reviewed_by_username
FROM
reviewables r
LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
LEFT JOIN users fu ON r.created_by_id = fu.id
LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
WHERE
r.created_at BETWEEN :start_date AND :end_date
),
flag_types AS (
SELECT
3 AS post_action_type_id, 'Off-topic' AS flag_type_name
UNION ALL
SELECT
4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
UNION ALL
SELECT
6 AS post_action_type_id, 'Notify_user' AS flag_type_name
UNION ALL
SELECT
7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
UNION ALL
SELECT
8 AS post_action_type_id, 'Spam' AS flag_type_name
UNION ALL
SELECT
10 AS post_action_type_id, 'Illegal' AS flag_type_name
UNION ALL
SELECT
NULL AS post_action_type_id, 'Something else' AS flag_type_name
)
SELECT
ft.flag_type_name AS Type,
COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
COUNT(*) AS Total
FROM
flag_data fd
LEFT JOIN post_actions pa
ON pa.post_id = fd.post_id
LEFT JOIN flag_types ft
ON pa.post_action_type_id = ft.post_action_type_id
GROUP BY
ft.flag_type_name
ORDER BY
Total DESC
사용된 매개변수
:start_date: 신고된 게시물을 필터링하기 위한 시작 날짜.:end_date: 신고된 게시물을 필터링하기 위한 종료 날짜.
CTE 설명
flag_data:
이 CTE는 신고된 게시물에 대한 상세 정보를 검색합니다. 여기에는 다음이 포함됩니다:
- 신고의 고유 ID(
flag_id), 신고된 게시물의 ID(post_id), 그리고 해당 게시물이 속한 토픽(topic_id). - 신고 자체에 대한 정보, 예를 들어 신고한 사용자(
flagged_by_username), 신고 날짜(flagged_date), 신고 유형(flag_type), 그리고 신고 이유(flag_reason). - 검토 과정에 대한 세부 정보, 예를 들어 신고를 검토한 모더레이터(
reviewed_by_username), 검토 결정(review_status), 그리고 검토 시간(reviewed_at).
flag_types:
이 CTE는 숫자형 게시물 작업 유형 ID를 사람이 읽을 수 있는 신고 유형 이름으로 매핑합니다:
3: 오프톱िक (Off-topic)4: 부적절 (Inappropriate)6: 사용자에게 알림 (Notify user)7: 모더레이터에게 알림 (Notify moderators)8: 스팸 (Spam)10: 불법 (Illegal)NULL: 기타 (Something else)
결과 설명
최종 쿼리는 신고 유형별로 신고 데이터를 집계하고 다음 메트릭을 제공합니다:
- 유형 (Type): 사람이 읽을 수 있는 신고 유형 이름 (예: 오프톱िक, 스팸).
- 사용자 신고 (Reported): 사용자가 제출한 신고의 수 (시스템이 생성한 신고 제외).
- 자동화 신고 (Automated): 시스템이나 봇이 생성한 신고의 수 (예:
spam_scanner_bot,system). - 합계 (Total): 각 유형에 대한 신고 총 수.
결과는 신고 총 수의 내림차순으로 정렬됩니다.
결과 예시
| 유형 | 사용자 신고 | 자동화 신고 | 합계 |
|---|---|---|---|
| 스팸 | 120 | 80 | 200 |
| 부적절 | 90 | 10 | 100 |
| 오프톱िक | 60 | 5 | 65 |
| 모더레이터 알림 | 30 | 0 | 30 |
| 불법 | 10 | 2 | 12 |
| 기타 | 5 | 0 | 5 |
정지 및 침묵 조치:
이 보고서는 지정된 날짜 범위 내에서 정지되거나 침묵 조치된 사용자를 나열합니다. 여기에는 정지 또는 침묵 날짜, 조치 기간, 그리고 사용자의 계정 생성 및 마지막 활동 날짜와 같은 세부 정보가 포함됩니다. 이 보고서는 모더레이션 활동을 모니터링하고 정지나 침묵으로 이어지는 사용자 행동 패턴을 식별하는 데 유용합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2026-01-01
SELECT
u.id AS user_id,
u.username,
u.name,
u.suspended_at,
u.suspended_till,
u.silenced_till,
u.created_at AS account_created_at,
u.last_seen_at,
u.flag_level,
u.admin,
u.moderator
FROM
users u
WHERE
(
u.suspended_at BETWEEN :start_date AND :end_date
OR u.silenced_till BETWEEN :start_date AND :end_date
)
ORDER BY
u.suspended_at DESC NULLS LAST,
u.silenced_till DESC NULLS LAST
사용된 매개변수
:start_date: 정지되거나 침묵 조치된 사용자를 필터링하기 위한 시작 날짜.:end_date: 정지되거나 침묵 조치된 사용자를 필터링하기 위한 종료 날짜.
결과 설명
이 쿼리는 지정된 날짜 범위 내에서 정지되거나 침묵 조치된 사용자의 목록을 검색합니다. 주요 열은 다음과 같습니다:
- 사용자 ID (User ID): 사용자의 고유 식별자.
- 사용자 이름 (Username): 사용자의 사용자 이름.
- 이름 (Name): 사용자의 전체 이름 (사용 가능한 경우).
- 정지 일시 (Suspended At): 사용자가 정지된 날짜.
- 정지 종료 일시 (Suspended Till): 사용자가 정지되어 있는 날짜.
- 침묵 종료 일시 (Silenced Till): 사용자가 침묵 조치되어 있는 날짜.
- 계정 생성 일시 (Account Created At): 사용자의 계정이 생성된 날짜.
- 마지막 확인 일시 (Last Seen At): 사용자가 플랫폼에서 마지막으로 활동한 시간.
- 신고 레벨 (Flag Level): 사용자의 현재 신고 레벨.
- 관리자 (Admin): 사용자가 관리자인지 여부 (true/false).
- 모더레이터 (Moderator): 사용자가 모더레이터인지 여부 (true/false).
결과는 정지 날짜(suspended_at)와 침묵 날짜(silenced_till)의 내림차순으로 정렬되며, null 값은 마지막에 표시됩니다.
결과 예시
| 사용자 ID | 사용자 이름 | 이름 | 정지 일시 | 정지 종료 일시 | 침묵 종료 일시 | 계정 생성 일시 | 마지막 확인 일시 | 신고 레벨 | 관리자 | 모더레이터 |
|---|---|---|---|---|---|---|---|---|---|---|
| 101 | user123 | John Doe | 2025-03-15 10:00 | 2025-04-15 10:00 | NULL | 2020-01-01 12:00 | 2025-03-14 18:00 | 2 | false | false |
| 102 | user456 | Jane Smith | NULL | NULL | 2025-03-20 18:00 | 2021-06-10 15:00 | 2025-03-19 20:00 | 1 | false | false |
| 103 | mod789 | Moderator1 | 2025-02-01 08:00 | 2025-03-01 08:00 | NULL | 2019-05-05 10:00 | 2025-01-31 22:00 | 3 | false | true |
| 104 | admin001 | AdminUser | NULL | NULL | 2025-03-25 12:00 | 2018-12-25 09:00 | 2025-03-24 16:00 | 0 | true | false |
합의된 신고 조치
이 보고서는 모더레이터에 의해 동의되어 조치가 취해진 신고에 초점을 맞춥니다. 신고를 유형별로 분류하고 신고 총 수, 조치에 소요된 중앙값 시간, 그리고 결과(예: 침묵 조치된 사용자, 삭제된 게시물)와 같은 메트릭을 제공합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
WITH flag_data AS (
SELECT
r.id AS flag_id,
p.id AS post_id,
p.topic_id,
p.raw AS flagged_item_text,
p.user_id AS post_author_id,
fu.username AS flagged_by_username,
r.created_at AS flagged_date,
r.type AS flag_type,
r.reviewable_by_moderator AS flag_source,
r.payload AS flag_reason,
r.status AS review_status,
rs.reviewed_by_id,
rs.reviewed_at,
rs.score AS review_score,
ru.username AS reviewed_by_username,
p.deleted_at AS post_deleted_at,
u.silenced_till AS user_silenced_till,
u.suspended_till AS user_suspended_till,
p.hidden_at AS post_hidden_at,
pa.post_action_type_id
FROM
reviewables r
LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
LEFT JOIN users fu ON r.created_by_id = fu.id
LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
LEFT JOIN users u ON p.user_id = u.id
LEFT JOIN post_actions pa ON pa.post_id = p.id
WHERE
r.created_at BETWEEN :start_date AND :end_date
AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
SELECT
3 AS post_action_type_id, 'Off-topic' AS flag_type_name
UNION ALL
SELECT
4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
UNION ALL
SELECT
6 AS post_action_type_id, 'Notify_user' AS flag_type_name
UNION ALL
SELECT
7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
UNION ALL
SELECT
8 AS post_action_type_id, 'Spam' AS flag_type_name
UNION ALL
SELECT
10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
median_time_to_act AS (
SELECT
COALESCE(ft.flag_type_name, fd.flag_type) AS flag_type,
ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date))) / 60) AS median_time_minutes
FROM
flag_data fd
LEFT JOIN flag_types ft
ON fd.post_action_type_id = ft.post_action_type_id
WHERE
fd.reviewed_at IS NOT NULL
AND (
ft.flag_type_name IS NOT NULL -- Include mapped flag types
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Include specific flag types for NULL results
)
GROUP BY
COALESCE(ft.flag_type_name, fd.flag_type)
)
SELECT
COALESCE(ft.flag_type_name, fd.flag_type) AS Type,
COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
COUNT(*) AS Total,
COALESCE(mta.median_time_minutes, 0) AS "Median time to act (minutes)",
COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "User silenced",
COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "User deleted",
COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post deleted",
COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post hidden"
FROM
flag_data fd
LEFT JOIN flag_types ft
ON fd.post_action_type_id = ft.post_action_type_id
LEFT JOIN median_time_to_act mta
ON COALESCE(ft.flag_type_name, fd.flag_type) = mta.flag_type
WHERE
ft.flag_type_name IS NOT NULL -- Include mapped flag types
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Include specific flag types for NULL results
GROUP BY
COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
Total DESC
사용된 매개변수
:start_date: 합의된 신고를 필터링하기 위한 시작 날짜.:end_date: 합의된 신고를 필터링하기 위한 종료 날짜.
CTE 설명
flag_data:
이 CTE는 동의되고 조치가 취해진 신고에 대한 상세 정보를 검색합니다. 여기에는 다음이 포함됩니다:
- 신고의 고유 ID(
flag_id), 신고된 게시물의 ID(post_id), 그리고 해당 게시물이 속한 토픽(topic_id). - 신고 자체에 대한 정보, 예를 들어 신고한 사용자(
flagged_by_username), 신고 날짜(flagged_date), 신고 유형(flag_type), 그리고 신고 이유(flag_reason). - 검토 과정에 대한 세부 정보, 예를 들어 신고를 검토한 모더레이터(
reviewed_by_username), 검토 결정(review_status), 그리고 검토 시간(reviewed_at). - 신고된 게시물에 대한 추가 정보, 예를 들어 게시물이 삭제되었거나 숨겨졌는지, 또는 작성자가 침묵 조치되거나 정지되었는지 여부.
flag_types:
이 CTE는 숫자형 게시물 작업 유형 ID를 사람이 읽을 수 있는 신고 유형 이름으로 매핑합니다:
3: 오프톱िक4: 부적절6: 사용자에게 알림7: 모더레이터에게 알림8: 스팸10: 불법
median_time_to_act:
이 CTE는 각 신고 유형에 대해 조치가 취해지는 데 걸린 중앙값 시간(분 단위)을 계산합니다. 시간은 신고 생성 시간(flagged_date)과 검토 시간(reviewed_at)의 차이로 계산됩니다.
결과 설명
최종 쿼리는 합의된 신고 데이터를 신고 유형별로 집계하고 다음 메트릭을 제공합니다:
- 유형 (Type): 사람이 읽을 수 있는 신고 유형 이름 (예: 오프톱िक, 스팸).
- 사용자 신고 (Reported): 사용자가 제출한 신고의 수 (시스템이 생성한 신고 제외).
- 자동화 신고 (Automated): 시스템이나 봇이 생성한 신고의 수 (예:
spam_scanner_bot,system). - 합계 (Total): 각 유형에 대한 신고 총 수.
- 조치 소요 중앙값 시간 (분) (Median Time to Act (Minutes)): 이 유형의 신고에 대해 조치가 취해지는 데 걸린 중앙값 시간(분).
- 사용자 침묵 (User Silenced): 사용자에게 침묵 조치가 취해진 결과로 이어진 신고의 수.
- 사용자 삭제 (User Deleted): 사용자에게 정지 조치가 취해진 결과로 이어진 신고의 수.
- 게시물 삭제 (Post Deleted): 게시물이 삭제된 결과로 이어진 신고의 수.
- 게시물 숨김 (Post Hidden): 게시물이 숨겨진 결과로 이어진 신고의 수.
결과는 신고 총 수의 내림차순으로 정렬됩니다.
결과 예시
| 유형 | 사용자 신고 | 자동화 신고 | 합계 | 조치 소요 중앙값 시간 (분) | 사용자 침묵 | 사용자 삭제 | 게시물 삭제 | 게시물 숨김 |
|---|---|---|---|---|---|---|---|---|
| 스팸 | 100 | 50 | 150 | 30 | 20 | 10 | 50 | 30 |
| 부적절 | 80 | 5 | 85 | 45 | 15 | 5 | 30 | 20 |
| 오프톱िक | 40 | 2 | 42 | 25 | 5 | 0 | 10 | 15 |
| 모더레이터 알림 | 20 | 0 | 20 | 60 | 0 | 0 | 5 | 10 |
| 불법 | 5 | 1 | 6 | 120 | 1 | 1 | 3 | 2 |
취해진 모더레이션 조치
이 보고서는 지정된 날짜 범위 내에서 취해진 모더레이션 조치의 요약을 제공합니다. 사용자나 자동화에 의해 신고된 콘텐츠, 삭제되거나 숨겨진 게시물, 발부된 경고, 삭제되거나 정지된 계정, 그리고 장기간 침묵 조치된 사용자를 포함하는 다양한 유형의 합의된 조치를 집계합니다. 각 카테고리는 사례 총 수와 함께 제시되어 모더레이션 활동에 대한 고수준의 개요를 제공합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
WITH flag_data AS (
SELECT
r.id AS flag_id,
p.id AS post_id,
p.topic_id,
p.raw AS flagged_item_text,
p.user_id AS post_author_id,
fu.username AS flagged_by_username,
r.created_at AS flagged_date,
r.type AS flag_type,
r.reviewable_by_moderator AS flag_source,
r.payload AS flag_reason,
r.status AS review_status,
rs.reviewed_by_id,
rs.reviewed_at,
rs.score AS review_score,
ru.username AS reviewed_by_username,
p.deleted_at AS post_deleted_at,
u.silenced_till AS user_silenced_till,
u.suspended_till AS user_suspended_till,
p.hidden_at AS post_hidden_at,
pa.post_action_type_id
FROM
reviewables r
LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
LEFT JOIN users fu ON r.created_by_id = fu.id
LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
LEFT JOIN users u ON p.user_id = u.id
LEFT JOIN post_actions pa ON pa.post_id = p.id
WHERE
r.created_at BETWEEN :start_date AND :end_date
AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
SELECT
3 AS post_action_type_id, 'Off-topic' AS flag_type_name
UNION ALL
SELECT
4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
UNION ALL
SELECT
6 AS post_action_type_id, 'Notify_user' AS flag_type_name
UNION ALL
SELECT
7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
UNION ALL
SELECT
8 AS post_action_type_id, 'Spam' AS flag_type_name
UNION ALL
SELECT
10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
SELECT
COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS user_flagged,
COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS automation_flagged
FROM
flag_data fd
LEFT JOIN flag_types ft
ON fd.post_action_type_id = ft.post_action_type_id
WHERE
ft.flag_type_name IS NOT NULL -- Include mapped flag types
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Include specific flag types for NULL results
),
warnings_issued AS (
SELECT
COUNT(*) AS warnings_count
FROM
user_warnings
WHERE
created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
SELECT
COUNT(CASE
WHEN uh.action = 1
AND (
LOWER(uh.context) LIKE '%deleted via review queue%' OR
LOWER(uh.context) LIKE '%to be a spammer%' OR
LOWER(uh.context) LIKE '%review%' OR
LOWER(uh.context) LIKE '%reviewable user rejected%'
)
THEN 1
END) AS accounts_deleted,
COUNT(CASE WHEN uh.action = 10 THEN 1 END) AS accounts_suspended
FROM
user_histories uh
WHERE
uh.created_at BETWEEN :start_date AND :end_date
),
posts_deleted_and_hidden AS (
SELECT
COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS posts_deleted,
COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS posts_hidden
FROM
flag_data fd
),
silences_issued AS (
SELECT
COUNT(*) AS silences_count
FROM
user_histories uh
WHERE
uh.action = 30 -- silence_user
AND uh.created_at BETWEEN :start_date AND :end_date
AND EXISTS (
SELECT 1
FROM users u
WHERE u.id = uh.target_user_id
AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
)
)
SELECT
'Content flagged by users' AS category,
fc.user_flagged AS "Number of Cases"
FROM flagged_content fc
UNION ALL
SELECT
'Content flagged by automation' AS category,
fc.automation_flagged AS "Number of Cases"
FROM flagged_content fc
UNION ALL
SELECT
'Posts deleted for violating terms' AS category,
pdh.posts_deleted AS "Number of Cases"
FROM posts_deleted_and_hidden pdh
UNION ALL
SELECT
'Posts hidden' AS category,
pdh.posts_hidden AS "Number of Cases"
FROM posts_deleted_and_hidden pdh
UNION ALL
SELECT
'Warnings Issued' AS category,
wi.warnings_count AS "Number of Cases"
FROM warnings_issued wi
UNION ALL
SELECT
'Accounts deleted' AS category,
vs.accounts_deleted AS "Number of Cases"
FROM violations_and_suspensions vs
UNION ALL
SELECT
'Accounts suspended' AS category,
vs.accounts_suspended AS "Number of Cases"
FROM violations_and_suspensions vs
UNION ALL
SELECT
'Users silenced for 10+ years' AS category,
si.silences_count AS "Number of Cases"
FROM silences_issued si
사용된 매개변수
:start_date: 모더레이션 조치를 필터링하기 위한 시작 날짜.:end_date: 모더레이션 조치를 필터링하기 위한 종료 날짜.
결과 설명
쿼리는 모더레이션 조치를 카테고리로 집계하고 각 카테고리에 대한 사례 총 수를 제공합니다. 카테고리는 다음과 같습니다:
- 사용자에 의해 신고된 콘텐츠: 일반 사용자가 신고한 게시물의 수 (시스템이 생성한 신고 제외).
- 자동화에 의해 신고된 콘텐츠: 자동화 시스템이나 봇이 신고한 게시물의 수 (예:
spam_scanner_bot,system). - 규정 위반으로 삭제된 게시물: 커뮤니티 가이드라인 또는 서비스 약관 위반으로 인해 삭제된 게시물의 수.
- 숨겨진 게시물: 다양한 이유로 인해 숨겨졌지만(삭제되지는 않았지만) 숨겨진 게시물의 수.
- 발부된 경고: 부적절한 행동이나 콘텐츠로 인해 사용자에게 발부된 경고의 수.
- 삭제된 계정: 스팸머로 신고되었거나 검토 대기열에서 거절된 것과 같은 위반으로 인해 삭제된 사용자 계정의 수.
- 정지된 계정: 위반으로 인해 특정 기간 동안 정지된 사용자 계정의 수.
- 10년 이상 침묵 조치된 사용자: 무기한 또는 장기간(10년 이상) 침묵 조치된 사용자의 수.
결과 예시
| 카테고리 | 사례 수 |
|---|---|
| 사용자에 의해 신고된 콘텐츠 | 150 |
| 자동화에 의해 신고된 콘텐츠 | 100 |
| 규정 위반으로 삭제된 게시물 | 50 |
| 숨겨진 게시물 | 30 |
| 발부된 경고 | 20 |
| 삭제된 계정 | 10 |
| 정지된 계정 | 15 |
| 10년 이상 침묵 조치된 사용자 | 5 |
개별 모더레이션 조치
이 보고서는 지정된 날짜 범위 내에서 취해진 합의된 개별 모더레이션 조치의 상세 로그를 제공합니다. 여기에는 조치를 수행한 사용자, 대상 사용자, 조치 날짜, 조치 카테고리(예: 신고된 콘텐츠, 삭제된 게시물, 발부된 경고), 그리고 조치의 맥락 또는 이유에 대한 정보가 포함됩니다. 이 보고서는 특정 모더레이션 결정을 감사하고 각 조치 뒤의 맥락을 이해하는 데 유용합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
WITH flag_data AS (
SELECT
r.id AS flag_id,
p.id AS post_id,
p.topic_id,
p.raw AS flagged_item_text,
p.user_id AS post_author_id,
fu.username AS flagged_by_username,
r.created_at AS flagged_date,
r.type AS flag_type,
r.reviewable_by_moderator AS flag_source,
r.payload AS flag_reason,
r.status AS review_status,
rs.reviewed_by_id,
rs.reviewed_at,
rs.score AS review_score,
ru.username AS reviewed_by_username,
p.deleted_at AS post_deleted_at,
u.silenced_till AS user_silenced_till,
u.suspended_till AS user_suspended_till,
p.hidden_at AS post_hidden_at,
pa.post_action_type_id
FROM
reviewables r
LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
LEFT JOIN users fu ON r.created_by_id = fu.id
LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
LEFT JOIN users u ON p.user_id = u.id
LEFT JOIN post_actions pa ON pa.post_id = p.id
WHERE
r.created_at BETWEEN :start_date AND :end_date
AND r.status = 1 -- Only include flags that were agreed with and action was taken
),
flag_types AS (
SELECT
3 AS post_action_type_id, 'Off-topic' AS flag_type_name
UNION ALL
SELECT
4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
UNION ALL
SELECT
6 AS post_action_type_id, 'Notify_user' AS flag_type_name
UNION ALL
SELECT
7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
UNION ALL
SELECT
8 AS post_action_type_id, 'Spam' AS flag_type_name
UNION ALL
SELECT
10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
SELECT
fd.flagged_by_username AS acting_user,
CAST(fd.post_author_id AS TEXT) AS target_user,
fd.flagged_date AS action_date,
'Content flagged by users' AS category,
fd.flagged_item_text AS context
FROM
flag_data fd
LEFT JOIN flag_types ft
ON fd.post_action_type_id = ft.post_action_type_id
WHERE
ft.flag_type_name IS NOT NULL
AND fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system')
UNION ALL
SELECT
fd.flagged_by_username AS acting_user,
CAST(fd.post_author_id AS TEXT) AS target_user,
fd.flagged_date AS action_date,
'Content flagged by automation' AS category,
fd.flagged_item_text AS context
FROM
flag_data fd
LEFT JOIN flag_types ft
ON fd.post_action_type_id = ft.post_action_type_id
WHERE
ft.flag_type_name IS NOT NULL
AND fd.flagged_by_username IN ('spam_scanner_bot', 'system')
),
posts_deleted_and_hidden AS (
SELECT
fd.reviewed_by_username AS acting_user,
CAST(fd.post_author_id AS TEXT) AS target_user,
fd.post_deleted_at AS action_date,
'Posts deleted for violating terms' AS category,
fd.flagged_item_text AS context
FROM
flag_data fd
WHERE
fd.post_deleted_at IS NOT NULL
UNION ALL
SELECT
fd.reviewed_by_username AS acting_user,
CAST(fd.post_author_id AS TEXT) AS target_user,
fd.post_hidden_at AS action_date,
'Posts hidden' AS category,
fd.flagged_item_text AS context
FROM
flag_data fd
WHERE
fd.post_hidden_at IS NOT NULL
),
warnings_issued AS (
SELECT
CAST(uw.created_by_id AS TEXT) AS acting_user,
CAST(uw.user_id AS TEXT) AS target_user,
uw.created_at AS action_date,
'Warnings Issued' AS category,
NULL AS context
FROM
user_warnings uw
WHERE
uw.created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
SELECT
CAST(uh.acting_user_id AS TEXT) AS acting_user,
CAST(uh.target_user_id AS TEXT) AS target_user,
uh.created_at AS action_date,
'Accounts deleted' AS category,
uh.context AS context
FROM
user_histories uh
WHERE
uh.created_at BETWEEN :start_date AND :end_date
AND uh.action = 1
AND (
LOWER(uh.context) LIKE '%deleted via review queue%' OR
LOWER(uh.context) LIKE '%to be a spammer%' OR
LOWER(uh.context) LIKE '%review%' OR
LOWER(uh.context) LIKE '%reviewable user rejected%'
)
UNION ALL
SELECT
CAST(uh.acting_user_id AS TEXT) AS acting_user,
CAST(uh.target_user_id AS TEXT) AS target_user,
uh.created_at AS action_date,
'Accounts suspended' AS category,
uh.context AS context
FROM
user_histories uh
WHERE
uh.created_at BETWEEN :start_date AND :end_date
AND uh.action = 10
),
silences_issued AS (
SELECT
CAST(uh.acting_user_id AS TEXT) AS acting_user,
CAST(uh.target_user_id AS TEXT) AS target_user,
uh.created_at AS action_date,
'Users silenced for 10+ years' AS category,
uh.context AS context
FROM
user_histories uh
WHERE
uh.action = 30 -- silence_user
AND uh.created_at BETWEEN :start_date AND :end_date
AND EXISTS (
SELECT 1
FROM users u
WHERE u.id = uh.target_user_id
AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
)
)
SELECT
acting_user,
target_user,
action_date,
category,
context
FROM flagged_content
UNION ALL
SELECT
acting_user,
target_user,
action_date,
category,
context
FROM posts_deleted_and_hidden
UNION ALL
SELECT
acting_user,
target_user,
action_date,
category,
context
FROM warnings_issued
UNION ALL
SELECT
acting_user,
target_user,
action_date,
category,
context
FROM violations_and_suspensions
UNION ALL
SELECT
acting_user,
target_user,
action_date,
category,
context
FROM silences_issued
사용된 매개변수
:start_date: 개별 모더레이션 조치를 필터링하기 위한 시작 날짜.:end_date: 개별 모더레이션 조치를 필터링하기 위한 종료 날짜.
결과 설명
쿼리는 개별 모더레이션 조치의 상세 로그를 제공하며, 여기에는 다음이 포함됩니다:
- 조치 수행 사용자 (Acting User): 조치를 수행한 모더레이터, 시스템 또는 사용자의 사용자 이름.
- 대상 사용자 (Target User): 조치가 대상이 된 사용자의 사용자 이름 또는 ID (예: 신고된 게시물의 작성자 또는 경고의 수신자).
- 조치 날짜 (Action Date): 조치가 발생한 날짜와 시간.
- 카테고리 (Category): 모더레이션 조치의 유형, 예를 들어:
- 사용자에 의해 신고된 콘텐츠
- 자동화에 의해 신고된 콘텐츠
- 규정 위반으로 삭제된 게시물
- 숨겨진 게시물
- 발부된 경고
- 삭제된 계정
- 정지된 계정
- 10년 이상 침묵 조치된 사용자
- 맥락 (Context): 조치와 관련된 추가 정보 또는 콘텐츠, 예를 들어 신고된 게시물의 텍스트나 정지 이유.
결과 예시
| 조치 수행 사용자 | 대상 사용자 | 조치 날짜 | 카테고리 | 맥락 |
|---|---|---|---|---|
| user123 | user456 | 2024-02-01 10:00 | 사용자에 의해 신고된 콘텐츠 | “이 게시물은 스팸 내용을 포함합니다.” |
| spam_scanner | user789 | 2024-02-02 12:00 | 자동화에 의해 신고된 콘텐츠 | “시스템에 의해 스팸으로 감지됨.” |
| mod001 | user456 | 2024-02-03 14:00 | 규정 위반으로 삭제된 게시물 | “예시 게시물 내용 |
| mod002 | user123 | 2024-02-04 16:00 | 숨겨진 게시물 | “게시물이 부적절하다고 판단됨.” |
| admin001 | user789 | 2024-02-05 18:00 | 발부된 경고 | NULL |
| admin002 | user456 | 2024-02-06 20:00 | 삭제된 계정 | “계정이 검토 대기열을 통해 삭제됨.” |
| mod003 | user123 | 2024-02-07 22:00 | 정지된 계정 | “반복적인 스팸으로 인해 사용자 정지.” |
| admin003 | user789 | 2024-02-08 08:00 | 10년 이상 침묵 조치된 사용자 | “극단적인 위반으로 인해 사용자 침묵 조치.” |