이 Data Explorer 보고서는 지정된 날짜 범위와 카테고리 내에서 생성된 주제에 대한 스태프(관리자 및 모더레이터)의 평균 응답 시간을 분석하도록 설계되었습니다.
이 보고서는 Discourse 사이트에서 스태프 상호작용의 효율성과 반응성을 이해하는 데 도움이 되며, 이는 커뮤니티 관리에 매우 중요합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-30
-- int_list :categories = 0
-- boolean :include_subcategories = false
WITH filtered_topics AS (
SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at
FROM topics t
WHERE
(
':categories' = '0'
OR t.category_id IN
(
SELECT id
FROM categories
WHERE id IN(:categories)
OR (:include_subcategories AND parent_category_id IN(:categories))
)
)
AND t.created_at >= :start_date
AND t.created_at < :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
),
staff_replies AS (
SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE (u.admin = true OR u.moderator = true)
AND p.deleted_at IS NULL
AND p.post_type = 1
GROUP BY p.topic_id
),
response_times AS (
SELECT
ft.topic_id,
ft.created_at AS topic_created_at,
sr.first_staff_reply,
ft.category_id as category_id,
EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours
FROM filtered_topics ft
LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id
)
SELECT
topic_id,
category_id,
topic_created_at,
response_time_hours as staff_response_time_hours
FROM response_times
ORDER BY topic_created_at ASC
SQL 쿼리 설명
이 보고서는 쿼리를 분해하기 위해 여러 개의 공통 테이블 표현식(CTE)을 사용합니다:
filtered_topics: 이 CTE는 날짜 범위, 카테고리, 하위 카테고리 포함 여부와 같은 입력 매개변수에 따라 주제를 필터링합니다. 삭제되지 않은 일반 아키타입(regular archetype) 주제만 고려되도록 보장합니다.staff_replies: 이 CTE는filtered_topicsCTE에서 식별된 주제에 대한 스태프(관리자 또는 모더레이터)의 첫 번째 답변을 식별합니다. 삭제된 게시물을 제외하고 메인 게시물(post_type = 1)만 고려합니다.response_times: 이 CTE는 주제 생성 시간과 첫 번째 스태프 답변 시간의 차이를 찾아 응답 시간을 계산합니다. 결과는 초에서 시간 단위로 변환됩니다.
response_times CTE의 최종 SELECT 문은 주제 ID, 카테고리 ID, 주제 생성 날짜, 시간 단위로 계산된 스태프 응답 시간을 가져오며, 결과를 주제 생성 날짜 순으로 정렬합니다.
매개변수
start_date(date): 주제 생성을 분석할 기간의 시작 날짜입니다.end_date(date): 주제 생성을 분석할 기간의 종료 날짜입니다.categories(int_list): 주제를 필터링할 카테고리 ID 목록입니다. 0으로 설정하면 모든 카테고리가 포함됩니다.include_subcategories(boolean): 지정된 카테고리의 하위 카테고리를 분석에 포함할지 여부를 결정하는 플래그입니다.
결과
topic_id: 주제의 고유 식별자입니다.category_id: 주제가 속한 카테고리입니다.topic_created_at: 주제가 생성된 날짜입니다.staff_response_time_hours: 스태프가 주제에 응답하는 데 걸린 시간(시간 단위)입니다. NULL 값은 해당 주제에 대한 스태프 응답이 없음을 나타냅니다.
결과 예시
| topic_id | category_id | topic_created_at | staff_response_time_hours |
|---|---|---|---|
| 101 | 5 | 2024-01-02 | 1.5 |
| 102 | 5 | 2024-01-02 | 3.2 |
| 103 | 12 | 2024-01-03 | NULL |
| 104 | 12 | 2024-01-04 | 0.75 |