このデータエクスプローラーレポートは、指定された期間とカテゴリ内で作成されたトピックに対するスタッフメンバー（管理者とモデレーター）の平均応答時間を分析するように設計されています。
このレポートは、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は、日付範囲、カテゴリ、サブカテゴリを含めるかどうかなどの入力パラメータに基づいてトピックをフィルタリングします。削除されていない通常のアーキタイプトピックのみが考慮されることを保証します。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 |