Für alle, die interessiert sind, hier ist, was zu funktionieren scheint:
-- [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
Dies sollte den Mittelwert und Median der Zeiten zwischen den Erstellungs-Zeitstempeln ergeben. Die Zeitstempel für Updates schienen problematisch zu sein (ich erhielt aus irgendeinem Grund einige negative Werte, wenn ich nur Durchschnittswerte berechnete).
Ich hatte einige KI-Unterstützung bei der Erstellung der Abfrage, daher ist es möglich, dass es Fehler gibt – und ich freue mich, wenn jemand Vorschläge hat, wie sie verbessert (oder Fehler korrigiert) werden können. Die Ergebnisse, die ich erhielt, schienen auf Überprüfungen kleinerer Gruppen sowie auf Änderungen, die sich aus der Erweiterung des Datumsbereichs ergaben und Trends aufwiesen, die mit den Erwartungen aus der Verwendung einer größeren Datenstichprobe übereinstimmten, vernünftig zu sein.