Time to first response by group members

커뮤니티 구축 데이터 및 보고
9

질문자님이 그룹 멤버별 평균 첫 응답 시간을 원하시는 것으로 추정됩니다. 만약 단순히 그룹 멤버들이 개별 주제(topic)에 응답하는 데 걸린 시간만 원하신다면, 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

이 쿼리는 그룹 멤버들이 개별 주제에 응답하는 데 걸린 시간을 알려줍니다. 결과는 카테고리별로 정리되어 있지만, 카테고리는 무시하셔도 됩니다.

개별 그룹 멤버에 대한 평균 응답 시간 데이터가 얼마나 의미 있는지 확신할 수 없습니다. 어려운 질문이나 다른 그룹 멤버들이 무시한 주제에 응답하는 그룹 멤버들에게 불리하게 작용할 수 있으므로, 이를任何形式的 성과 평가에 사용하는 것은 신중해야 합니다. 이러한 점을 고려하여, 그룹 멤버들의 평균 응답 시간과 그들이 그룹 내에서 첫 번째로 응답한 주제의 수를 반환하는 쿼리를 아래에 제공합니다:

-- [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