Time to first response by group members

データとレポート
3

それは使えません。正直なところ、これがどのように機能していたのかさえわかりません。すぐに更新版を投稿し、準備ができたら通知します。

編集：@IreneT
元のクエリの修正版はこちらです。そのクエリは無視して、代わりにこの返信に投稿した他のクエリを見てください。クエリに関する質問がある場合、またはクエリに必要なパラメーターを追加する際に問題が発生した場合は、お知らせください。今日のテストから、Data Explorer クエリを保存した後、パラメーター入力フィールドがクエリの下に表示されるようにページを更新する必要があることがわかりました。（これは私のローカル開発サイトでのみ発生する可能性のある不具合かもしれません。）

-- [params]
-- int :months_ago = 1

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
),
staff_responses AS (
SELECT
DISTINCT ON (p.topic_id)
p.topic_id,
p.created_at,
t.category_id,
EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time
FROM posts p
JOIN topics t
ON t.id = p.topic_id
AND t.category_id = ANY ('{46,25,43,40,44,35,22,7,20,17,6,12}'::int[])
JOIN users u
ON u.id = p.user_id
WHERE p.post_number > 1
AND u.admin = 't' OR u.moderator = 't'
ORDER BY p.topic_id, p.created_at
),
user_topics AS (
SELECT
t.id
FROM topics t
JOIN users u
ON u.id = t.user_id
WHERE u.admin = 'f' AND u.moderator = 'f'
)

SELECT
sr.category_id,
AVG(sr.response_time) AS "Average First Response Time",
COUNT(1) AS "Topics Responded to"
FROM staff_responses sr
JOIN query_period qp
ON sr.created_at >= qp.period_start
AND sr.created_at <= qp.period_end
JOIN user_topics t
ON t.id = sr.topic_id
GROUP BY sr.category_id

変更されたのは次のとおりです。

--DATE_TRUNC('minute', p.created_at - t.created_at) AS response_time
EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time

古いクエリをテストせずに書いたとしたら驚きです。いずれにせよ、更新されたバージョンは期待どおりに機能します。

記憶によれば、このクエリは特定のサイト用に書かれたもので、Meta に投稿することを意図したものではありませんでした。スタッフの応答時間に関する情報を取得するための、さらに便利なクエリをいくつか紹介します。

指定された期間内に作成されたトピックに対する最初のグループ応答までの時間

指定されたグループのメンバーによる、指定されたグループのメンバーではないユーザーによって作成された「通常」（ダイレクトメッセージではない）トピックへの最初の応答までの時間を返します。クエリの :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(MINUTE FROM (p.created_at - t.created_at)) 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,
    response_time_minutes
FROM group_response_times
WHERE row_num = 1
ORDER BY category_id, response_time_minutes

カテゴリごとの最初のグループ応答までの平均時間：

前のクエリと同じロジックを使用しますが、指定された期間内に指定されたグループのメンバーではないユーザーによって作成されたトピックについて、指定されたグループのメンバーによる最初の応答までの 平均 時間を返します。前のクエリと同様に、: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(MINUTE FROM (p.created_at - t.created_at)) 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,
    AVG (response_time_minutes) AS average_response_time_minutes,
    COUNT(*) AS num_topics_with_staff_responses
FROM group_response_times
WHERE row_num = 1
GROUP BY category_id

@JammyDodger、OP のクエリをこの投稿の最後の 2 つのクエリに置き換えることを検討してください。また、タイトルを「グループメンバーによる最初の応答までの時間」のようなものに更新すると良いでしょう。

「いいね！」 3