Presumo che tu voglia il tempo medio alla prima risposta per membro del gruppo; se vuoi solo il tempo impiegato dai membri del gruppo per rispondere ai singoli argomenti, usa la versione corretta della prima query nell’OP:
-- [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
Questo ti dirà il tempo impiegato dai membri del gruppo per rispondere ai singoli argomenti. I risultati sono organizzati per categoria, ma la categoria può essere ignorata.
Non sono sicuro di quanto possano essere significativi i dati sui tempi medi di risposta per i singoli membri del gruppo. Sarei cauto nell’utilizzarli per qualsiasi tipo di valutazione delle prestazioni, poiché potrebbero penalizzare i membri del gruppo che rispondono a domande o argomenti difficili che altri membri del gruppo hanno ignorato. Tenendo conto di ciò, ecco una query che restituisce i tempi medi di risposta per i membri del gruppo e il numero di argomenti a cui sono stati il primo membro del gruppo a rispondere:
-- [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
staff_user_id,
ROUND(AVG(response_time_minutes)::numeric, 2) AS average_response_time_minutes,
COUNT(*) AS number_of_topics_responded_to
FROM group_response_times
WHERE row_num = 1
GROUP BY staff_user_id
ORDER BY average_response_time_minutes