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

Datos e informes
1

Al igual que el tiempo de primera respuesta, estoy buscando una forma de determinar cuál es el tiempo promedio (durante cualquier período de tiempo) entre las dos últimas respuestas en los temas de una lista seleccionada de categorías.

El caso de uso para esto es determinar si una configuración de cierre automático es apropiada.

Tengo instalado el plugin Data Explorer, pero no sé lo suficiente sobre el esquema de la base de datos (o, para el caso, no recuerdo mucho de mi curso de base de datos/SQL de la universidad a principios de los 90) para crear el informe yo mismo.

Agradecería indicaciones y sugerencias sobre cómo extraer estos datos.

ETA: Parece que el enfoque es hacer una unión entre temas y publicaciones (para poder filtrar por categoría), y luego encontrar las publicaciones con las dos marcas de tiempo de actualización más recientes y diferenciarlas.

Dado que el propósito del ejercicio no depende de si la última es una respuesta a la penúltima, sino de determinar la diferencia máxima de tiempo entre las dos respuestas más recientes (independientemente de su relación específica) para ver si el tema era lo suficientemente antiguo como para ser cerrado, y luego calcular el promedio en todas las categorías (o por categoría, lo que probablemente sería más útil),

Pensando un poco más al respecto, parece que esto podría ser solo una variación de la consulta que se utiliza en el proceso rake topics:auto_close (la diferencia es que esa utiliza el tiempo actual en lugar de diferenciar las dos publicaciones más recientes en el tema).

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.

3

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.