커스텀 보고서 작성: 토픽에서 마지막 두 응답 사이의 시간

커뮤니티 구축 데이터 및 보고
2

관심 있는 분들을 위해, 이렇게 하면 되는 것 같습니다:

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

이 쿼리는 생성 타임스탬프 사이의 시간 차이의 평균과 중앙값을 반환해야 합니다. 업데이트 타임스탬프는 문제가 되는 것 같았습니다(단순히 평균만 계산했을 때 일부 값이 음수로 나오는 이상한 현상이 발생했기 때문입니다).

쿼리를 작성할 때 AI의 도움을 받았기 때문에 오류가 있을 수 있습니다. 개선 방법이나 오류 수정에 대한 제안이 있다면 환영합니다. 작은 그룹에 대한 검증을 통해 얻은 결과들은 합리적으로 보였으며, 날짜 범위를 넓혔을 때 더 큰 데이터 샘플에서 기대했던 추세와 일치하는 변화가 나타났습니다.