Time to first group response for topics created within a given time period
Returns the time to first response by a member of a given group to “regular” (not Personal Message) topics created by a user who is not a member of the given group. The query’s :group_name parameter is set to “staff” by default. With that value, it will give the time to first response by staff members. You can change the value of that parameter to get response times for different groups. For example “customer_support”.
Note that dates should technically be supplied in the form yyyy-mm-dd, but the query would also accept dates in the form 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
Average time to first group response per category:
Uses the same logic as the previous query, but returns the average time to the first response by members of the given group per category for topics created by users who are not members of the given group within the time period set by the :start_date and :end_date parameters. As with the previous query, if the :group_name parameter is left at its default value of “staff”, it will return the average staff first response times for regular topics created by non-staff users.
-- [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
