대시보드 보고서 - 응답 없는 주제

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

Hi Sophie,

반응이 없는 토픽을 연도, 월 또는 다른 시간 간격별로 집계할 수 있도록 쿼리를 수정하려면, date_trunc 함수에 원하는 간격을 지정하는 매개변수를 추가하면 됩니다.

예를 들어:

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Options: day, week, month, year

WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT t.id, t.created_at, MIN(p.post_number) first_reply
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
    WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
      AND (
        :category_id IS NULL
        OR t.category_id = :category_id
        OR (
          :include_subcategories
          AND t.category_id IN (
            SELECT id FROM categories WHERE parent_category_id = :category_id
          )
        )
      )
    GROUP BY t.id
  ) tt
  WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT 
  date_trunc(:interval, nrt.created_at)::date AS period, 
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC

매개변수를 제거하고 싶다면, 다음과 같은 쿼리를 사용할 수도 있습니다:

WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT t.id, t.created_at, MIN(p.post_number) first_reply
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
    WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
    GROUP BY t.id
  ) tt
  WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT 
  date_trunc('year', nrt.created_at)::date AS period, 
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC