Je cherche à créer un rapport personnalisé - temps entre les deux dernières réponses sur un sujet

Communauté Données et rapports
2

Pour ceux qui sont intéressés, voici ce qui semble fonctionner :

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

Cela devrait donner la moyenne et la médiane des temps entre les horodatages de création. Les horodatages de mise à jour semblaient problématiques (j’ai obtenu des valeurs négatives pour une raison quelconque en faisant simplement des moyennes).

J’ai eu une aide de l’IA pour créer la requête, il est donc possible qu’il y ait des erreurs - et je suis heureux si quelqu’un a des suggestions sur la façon de l’améliorer (ou de corriger des erreurs). Les résultats que j’ai obtenus semblaient raisonnables sur la base de vérifications effectuées sur des groupes plus petits, ainsi que des changements résultant de l’augmentation de la plage de dates entraînant des tendances qui correspondaient aux attentes que j’avais de l’utilisation d’un échantillon de données plus large.