모더레이터 플래그 처리 효율 보고서

커뮤니티 구축 데이터 및 보고
1

이 Data Explorer SQL 쿼리는 포럼에서 사용자가 게시물에 대해 올린 플래그를 처리하는 중재자의 효율성과 반응 속도에 대한 통찰력을 제공하도록 설계되었습니다.

이 보고서는 지정된 기간 동안 사이트별 중재자마다 처리한 플래그의 수와 플래그 해결에 걸린 평균 시간을 보여줍니다.

중재자가 플래그에 얼마나 빠르고 효과적으로 대응하는지를 이해하면 관리자가 포럼이 모든 사용자에게 안전하고 환영받는 공간으로 유지되도록 할 수 있습니다.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-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

SQL 쿼리 설명

매개변수

이 쿼리는 데이터를 분석할 기간을 정의하기 위해 두 개의 매개변수를 사용합니다:

  • :start_date: 분석하려는 기간의 시작 날짜로, YYYY-MM-DD 형식입니다.
  • :end_date: 분석하려는 기간의 종료 날짜로, 역시 YYYY-MM-DD 형식입니다.

CTE(공용 테이블 표현식) 설명

  • period_actions: 이 CTE는 지정된 시간 기간 내에 발생한 플래그를 선택하며, 특정 플래그 유형에 초점을 맞춥니다. 플래그가 생성된 시점부터 중재자에 의해 동의, 이의 제기 또는 보류된 시점까지의 해결에 걸린 시간(분 단위)을 계산합니다.
  • moderator_actions: 이 CTE는 period_actions의 동작 중 중재자가 응답한 것만 포함하도록 필터링합니다. 각 동작에 대해 책임 있는 중재자를 식별합니다.
  • moderator_stats: 이 CTE는 moderator_actions의 데이터를 집계하여, 각 중재자가 처리한 플래그의 총 수와 그들이 처리한 플래그의 평균 해결 시간(분 단위)을 계산합니다.

결과

쿼리의 최종 출력은 처리한 플래그 수 기준으로 정렬된 중재자 목록을 제공하며, 보조 정렬 기준으로는 평균 해결 시간(오름차순)을 사용합니다. 각 중재자에 대해 보고서는 다음을 표시합니다:

  • moderator_username: 중재자의 사용자 이름.
  • handled_flags: 지정된 기간 동안 중재자가 처리한 플래그의 총 수.
  • avg_resolution_time_minutes: 중재자가 플래그를 해결하는 데 걸린 평균 시간으로, 소수점 둘째 자리까지 반올림됩니다.

결과 예시

moderator_username handled_flags avg_resolution_time_minutes
Moderator1 343 39.41
Moderator2 70 30.51
Moderator3 63 42.21