グループメンバーによる初回返信までの時間

コミュニティ データとレポート
9

グループメンバーごとの最初の応答時間の平均値を知りたいということでしょうか。もし単に、グループメンバーが個々のトピックに応答するのにかかった時間だけを知りたいのであれば、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