指定された期間内に作成されたトピックに対する、グループからの初回応答までの時間
指定されたグループのメンバーが、そのグループのメンバーではないユーザーによって作成された「通常」（個人メッセージではない）トピックに初回応答するまでの時間を返します。クエリの :group_name パラメータはデフォルトで「staff」に設定されています。この値を使用すると、スタッフメンバーによる初回応答までの時間が取得できます。このパラメータの値を変更することで、異なるグループ（例：「customer_support」）の応答時間を取得することも可能です。
日付は技術的には yyyy-mm-dd の形式で指定する必要がありますが、このクエリでは dd-mm-yyyy の形式の日付も受け付けます。
-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff
WITH group_response_times AS (
SELECT
t.category_id,
t.id AS topic_id,
EXTRACT(EPOCH FROM (p.created_at - t.created_at)) / 60 AS response_time_minutes,
p.user_id AS staff_user_id,
ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.user_id NOT IN (SELECT user_id
FROM group_users gu JOIN groups g ON g.id = gu.group_id
WHERE gu.user_id > 0 AND g.name = :group_name)
AND p.user_id IN (SELECT user_id
FROM group_users gu JOIN groups g ON g.id = gu.group_id
WHERE gu.user_id > 0 AND g.name = :group_name)
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.post_type = 1
AND p.deleted_at IS NULL
AND t.created_at BETWEEN :start_date AND :end_date
)
SELECT
category_id,
topic_id,
staff_user_id,
ROUND(response_time_minutes::numeric, 2) AS response_time_minutes
FROM group_response_times
WHERE row_num = 1
ORDER BY category_id, response_time_minutes
カテゴリごとのグループからの初回応答までの平均時間：
前のクエリと同じロジックを使用しますが、 :start_date および :end_date パラメータで設定された期間内に、指定されたグループのメンバーではないユーザーによって作成されたトピックに対して、そのグループのメンバーが初回応答するまでの 平均 時間をカテゴリごとに返します。前のクエリと同様に、:group_name パラメータの値をデフォルトの「staff」のままにしておくと、スタッフではないユーザーによって作成された通常トピックに対するスタッフの初回応答時間の平均が返されます。
-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff
WITH group_response_times AS (
SELECT
t.category_id,
t.id AS topic_id,
EXTRACT(EPOCH FROM (p.created_at - t.created_at)) / 60 AS response_time_minutes,
p.user_id AS staff_user_id,
ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.user_id NOT IN (SELECT user_id
FROM group_users gu JOIN groups g ON g.id = gu.group_id
WHERE gu.user_id > 0 AND g.name = :group_name)
AND p.user_id IN (SELECT user_id
FROM group_users gu JOIN groups g ON g.id = gu.group_id
WHERE gu.user_id > 0 AND g.name = :group_name)
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.post_type = 1
AND p.deleted_at IS NULL
AND t.created_at BETWEEN :start_date AND :end_date
)
SELECT
category_id,
ROUND(AVG (response_time_minutes)::numeric, 2) AS average_response_time_minutes,
COUNT(*) AS num_topics_with_staff_responses
FROM group_response_times
WHERE row_num = 1
GROUP BY category_id
