Vorrei creare un report personalizzato - tempo tra le ultime due risposte su un argomento

Comunità Dati e reportistica
2

Per chi fosse interessato, ecco cosa sembra funzionare:

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

Questo dovrebbe fornire la media e la mediana dei tempi tra i timestamp di creazione. I timestamp di aggiornamento sembravano problematici (ho ottenuto alcuni valori negativi per qualche motivo quando facevo solo le medie).

Ho avuto un po’ di assistenza AI nella creazione della query, quindi è possibile che ci siano degli errori e sarei felice se qualcuno avesse suggerimenti su come migliorarla (o correggere eventuali errori). I risultati che ho ottenuto sembravano ragionevoli in base ai controlli effettuati su gruppi più piccoli, nonché ai cambiamenti derivanti dall’aumento dell’intervallo di date che hanno portato a tendenze in linea con le aspettative che avevo dall’uso di un campione di dati più ampio.