Buscando crear un informe personalizado: tiempo entre las dos últimas respuestas en un tema

Comunidad Datos e informes
2

Para quien esté interesado, esto es lo que parece funcionar:

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

Esto debería dar la media y la mediana de las marcas de tiempo de creación. Las marcas de tiempo de actualización parecían problemáticas (obtuve algunos valores negativos por alguna razón al hacer solo promedios).

Tuve algo de ayuda de IA para crear la consulta, por lo que es posible que haya errores, y estaré encantado si alguien tiene sugerencias sobre cómo mejorarla (o corregir cualquier error). Los resultados que obtuve parecían razonables basándome en comprobaciones realizadas en grupos más pequeños, así como en cambios resultantes de aumentar el rango de fechas que resultaron en tendencias que se alineaban con las expectativas que tenía de usar una muestra de datos más grande.