カスタムレポートで、トピックの直近2回の返信間隔を知りたい

[hendersj]

最終応答までの時間と同様に、選択したカテゴリのトピックにおける最後の 2 つの応答間の平均時間（任意の期間）を決定する方法を探しています。

このユースケースは、自動クローズ設定が適切かどうかを判断するために使用されます。

データエクスプローラープラグインはインストールされていますが、データベーススキーマについて（あるいは 90 年代初頭の大学のデータベース/SQL コースで学んだことのほとんど）十分な知識がないため、自分でレポートを作成できません。

このデータを取得する方法についてのポインタや提案をいただけると幸いです。

ETA: トピックと投稿を結合して（カテゴリでフィルタリングできるように）、最新の 2 つの更新タイムスタンプを持つ投稿を見つけて、それらの差を計算するというアプローチのようです。

演習のポイントは、最新のものが 2 番目に新しいものへの返信であるかどうかに関係なく、トピックがクローズされるのに十分な古いかどうかを確認するために、2 つの最も新しい返信間の最大時間差を決定し、次にすべてのカテゴリ（またはカテゴリごと、おそらくより役立つ）の平均を計算することです。

もう少し考えてみると、これは rake topics:auto_close プロセスで使用されるクエリのバリエーションにすぎないようです（最新の 2 つの投稿を差分するのではなく、現在時刻を使用するという点で異なります）。

[hendersj]

ご興味のある方のために、機能すると思われるものを以下に示します。

-- [params]
-- null date :start_date
-- null date :end_date
-- null int_list :category_ids

WITH RankedPosts AS (
    SELECT
        p.topic_id,
        p.created_at,
        ROW_NUMBER() OVER (PARTITION BY p.topic_id ORDER BY p.post_number DESC) AS rank
    FROM
        posts p
    WHERE
        p.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM topics t
            WHERE t.id = p.topic_id
            AND t.category_id IN (:category_ids)
        )
),
FilteredPosts AS (
    SELECT
        topic_id,
        created_at,
        rank
    FROM
        RankedPosts
    WHERE
        rank <= 2
),
PostDifferences AS (
    SELECT
        topic_id,
        EXTRACT(days FROM (MAX(created_at) FILTER (WHERE rank = 1) - MAX(created_at) FILTER (WHERE rank = 2)))::numeric(9,2) AS days_difference
    FROM
        FilteredPosts
    GROUP BY
        topic_id
)
SELECT
    t.category_id,
    AVG(pd.days_difference) AS avg_days_difference,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pd.days_difference) AS median_days_difference
FROM
    topics t
JOIN
    PostDifferences pd ON t.id = pd.topic_id
GROUP BY
    t.category_id
ORDER BY
    avg_days_difference DESC

これにより、作成タイムスタンプ間の時間の平均値と中央値が得られるはずです。更新タイムスタンプは問題があるように見えました（平均を計算しただけで、なぜか負の値が得られました）。

クエリの作成にはAIの支援を受けましたので、間違いがある可能性があります。改善する方法やエラーを修正する方法について、誰か提案があれば喜んで受け入れます。得られた結果は、小規模なグループでのチェック、および日付範囲を広げることによる期待に沿った傾向（より大きなデータサンプルから得たもの）の結果から、妥当なものに見えました。