주제별 멤버 평균 답변 수 (관리자 제외)

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

이런 방식으로 원하는 숫자를 얻을 수 있을 것 같습니다:

-- [params]
-- date :start_date
-- date :end_date


WITH staff_data AS (

    SELECT user_id, true as is_staff
    FROM group_users
    WHERE group_id = 3
),

month_stats AS (

    SELECT
        date_trunc('month', p.created_at)::date AS month,
        COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
        COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
        COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
        COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
        COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
        COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
    FROM posts p
      LEFT JOIN topics t ON t.id = p.topic_id
      LEFT JOIN staff_data s ON p.user_id = s.user_id
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
      AND t.archetype = 'regular'
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY month
)

SELECT
    ms.month AS "Month",
    ms.total_topics AS "All Topics",
    ms.total_posts AS "All Posts",
    ms.non_staff_posts AS "Non-staff posts",
    ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Non-staff posts (% of total)",
    ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Average non-staff posts per topic",
    ms.non_staff_users AS "Non-staff users who posted",
    ms.staff_posts AS "Staff posts",
    ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Staff posts (% of total)",
    ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Average staff posts per topic",
    ms.staff_users AS "Staff users who posted"
FROM month_stats ms
ORDER BY "Month"

이렇게 하면 다음과 같은 결과가 나옵니다:

Staff and non-staff posts (monthly)
Staff and non-staff posts (monthly)1396×712 24.7 KB

그리고 참고로 설명을 조금 덧붙여 보겠습니다: :slight_smile:

이 쿼리는 특정 기간 내 포럼 활동의 월별 통계 요약을 제공하도록 설계되었습니다. 특히 ‘스태프’와 ‘비스태프’ 사용자의 기여도를 구분하는 데 초점을 맞춥니다. 계산되는 지표에는 생성된 전체 토픽 수, 작성된 전체 게시물 수, 게시물을 작성한 고유한 비스태프 사용자 수, 비스태프가 작성한 게시물의 수와 비율, 토픽당 평균 비스태프 게시물 수, 그리고 스태프에 해당하는 동일한 수치들이 포함됩니다. 이 정보는 사용자 참여도, 콘텐츠 생성량, 그리고 포럼 토론에서 스태프와 비스태프 멤버의 참여율을 파악하는 데 유용한 통찰력을 제공합니다. 쿼리는 ‘일반’(비-PM) 토픽만 고려하고, 삭제된 게시물이나 토픽, 속삭임/소형 게시물/관리자 조치, 시스템 사용자의 게시물을 제외함으로써 정확성을 보장합니다. 지정된 날짜 범위 내에서만 데이터를 처리합니다.

이 쿼리에서 ‘스태프’ 기준은 자동 @staff 그룹에 속하는 사용자로, 관리자(Admin)와 모더레이터(Moderator)를 모두 포함합니다 - 단, 관리자만 대상으로 하거나, 엄밀히 '사이트 스태프’가 아닌 임직원으로 구성된 커스텀 그룹을 대상으로하도록 조정할 수도 있습니다. 모더레이터를 확실히 제외하고 싶다면, 시작 부분의 group_id를 '1’로 변경하면 됩니다. :+1:

이런 종류의 것을 찾고 계신가요?