대시보드 보고서 - 플래그 상태

[SaraDev (Sara Devlaeminck)]

이것은 플래그 상태 대시보드 리포트의 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
),
poster_data AS (
    SELECT pa.id,
           p.user_id AS poster_id,
           p.topic_id,
           p.post_number,
           u.username_lower AS poster_username,
           u.uploaded_avatar_id AS poster_avatar_id
    FROM period_actions pa
    JOIN posts p ON p.id = pa.post_id
    JOIN users u ON u.id = p.user_id
),
flagger_data AS (
    SELECT pa.id,
           u.id AS flagger_id,
           u.username_lower AS flagger_username,
           u.uploaded_avatar_id AS flagger_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = pa.user_id
),
staff_data AS (
    SELECT pa.id,
           u.id AS staff_id,
           u.username_lower AS staff_username,
           u.uploaded_avatar_id AS staff_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id)
),
flag_types AS (
    SELECT pat.id,
           pat.id AS flag_type
    FROM post_action_types pat
)
SELECT
    CASE 
        WHEN pat.flag_type = 3 THEN 'off_topic'
        WHEN pat.flag_type = 4 THEN 'inappropriate'
        WHEN pat.flag_type = 6 THEN 'notify_user'
        WHEN pat.flag_type = 7 THEN 'notify_moderators'
        WHEN pat.flag_type = 8 THEN 'spam'
    END "type",
    pa.post_id as flagged_post_id,
    pd.poster_id as poster_user_id,
    fd.flagger_id as flagger_user_id,
    DATE(pa.created_at) as flag_created,
    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'
    END "resolution",
    sd.staff_id as assigned_user_id,
    ROUND(pa.time_to_resolution_minutes,2) as "resolution_time (minutes)"
FROM period_actions pa
JOIN poster_data pd ON pd.id = pa.id
JOIN flagger_data fd ON fd.id = pa.id
LEFT JOIN staff_data sd ON sd.id = pa.id
JOIN flag_types pat ON pat.id = pa.post_action_type_id
ORDER BY pa.created_at ASC

SQL 쿼리 설명

이 쿼리는 공통 테이블 표현식(CTE)을 사용하여 데이터를 구성하며, 이는 최종 SELECT 문에서 리포트를 컴파일하는 데 사용되는 데이터의 하위 집합을 준비합니다.

매개변수

쿼리는 두 개의 매개변수를 받아들입니다:

• :start_date: 리포트를 생성할 기간의 시작일.
• :end_date: 리포트를 생성할 기간의 종료일.

CTE

period_actions

이 CTE는 지정된 날짜 범위 내에 있고 특정 유형(3, 4, 6, 7, 8)인 게시글 작업(플래그)을 선택합니다. 이 유형들은 서로 다른 플래그 사유에 해당합니다. 플래그의 생성 시각과 응답(동의, 반대, 보류) 시각 사이의 차이를 구하여 time_to_resolution_minutes를 계산합니다.

poster_data

이 CTE는 period_actions를 posts와 users 테이블과 조인하여 플래그가 지정된 게시글의 작성자에 대한 정보(사용자 ID, 토픽 ID, 게시글 번호, 사용자 이름 등)를 검색합니다.

flagger_data

이 CTE는 period_actions를 users 테이블과 조인하여 게시글에 플래그를 지정된 사용자의 정보(사용자 ID, 사용자 이름 등)를 가져옵니다.

staff_data

이 CTE는 플래그를 동의, 반대 또는 보류한 스태프 멤버의 ID를 기준으로 period_actions를 users 테이블과 조인하여 플래그를 처리한 스태프 멤버의 정보를 검색합니다.

flag_types

이 CTE는 post_action_types 테이블에서 ID를 단순히 선택하며, 이는 최종 SELECT 문에서 플래그 유형을 결정하는 데 사용됩니다.

최종 SELECT

최종 SELECT 문은 모든 CTE를 결합하여 포괄적인 리포트를 제시합니다. 다음 컬럼을 포함합니다:

• type: flag_type ID에 의해 결정된 플래그 유형.
• flagged_post_id: 플래그가 지정된 게시글의 ID.
• poster_user_id: 플래그가 지정된 콘텐츠를 게시한 사용자의 ID.
• flagger_user_id: 콘텐츠에 플래그를 지정된 사용자의 ID.
• flag_created: 플래그가 생성된 날짜.
• resolution: 플래그의 해결 상태(동의, 반대, 보류).
• assigned_user_id: 플래그를 처리한 스태프 멤버의 ID.
• resolution_time (minutes): 플래그 해결에 걸린 시간(분 단위, 소수점 둘째 자리까지 반올림).

리포트는 플래그 생성 날짜(pa.created_at) 기준으로 오름차순으로 정렬됩니다.

결과 예시

type	flagged_post	poster_user	flagger_user	flag_created	resolution	assigned_user	resolution_time (minutes)
off_topic	Example_Post_Link1	example_user1	example_user2	2023-01-01	agreed	example_user3	4.56
inappropriate	Example_Post_Link2	example_user1	example_user2	2023-01-02	disagreed	example_user3	38.76
spam	Example_Post_Link3	example_user1	example_user2	2023-01-03	deferred	example_user3	79.32
—	—	—	—	—	—	—	—