Procurando construir um relatório personalizado - tempo entre as duas últimas respostas em um tópico

Comunidade Dados e relatórios
2

Para quem estiver interessado, eis o 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

Isso deve fornecer a média e a mediana dos tempos entre os timestamps de criação. Os timestamps de atualização pareciam problemáticos (obtive alguns valores negativos por algum motivo ao apenas fazer médias).

Tive alguma ajuda de IA na criação da consulta, então é possível que haja erros - e ficarei feliz se alguém tiver sugestões sobre como melhorá-la (ou corrigir quaisquer erros). Os resultados que obtive pareceram razoáveis com base em verificações feitas em grupos menores, bem como em mudanças resultantes do aumento do intervalo de datas, resultando em tendências que se alinharam com as expectativas que eu tinha de usar uma amostra de dados maior.