사용자별 월별 해결된 질문 및 현재 할당된 주제

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

:discourse: 이 보고서를 사용하려면 Discourse Solved 플러그인이 활성화되어 있어야 합니다.

이 Data Explorer 보고서는 특정 기간 내에 특정 그룹의 구성원들이 수행한 활동에 대한 개요를 제공합니다. 구체적으로, 해결된 질문과 할당된 주제라는 두 가지 주요 활동에 초점을 맞춥니다.

이 보고서는 관리자가 그룹 구성원들의 기여도와 업무량을 이해할 수 있도록 설계되어, 더 나은 자원 배분과 적극적인 기여자에 대한 인정을 촉진합니다.

--[params]
-- string :group_name_filter = staff
-- string :date_trunc = month
-- null user_list :user_list
-- boolean :userlist_filter = false
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH group_users_filtered AS (
    SELECT 
        gu.user_id
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    WHERE g.name = :group_name_filter
),
user_groups AS (
    SELECT 
        gu.user_id, 
        STRING_AGG(g.name, ', ') AS group_names
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    GROUP BY gu.user_id
),
questions_solved AS (
    SELECT 
        p.user_id, 
        DATE_TRUNC(:date_trunc, dsta.created_at) AS month,
        COUNT(*) AS total_solved
    FROM discourse_solved_solved_topics dsst
    JOIN discourse_solved_topic_answer dsta on dsta.solved_id = dsst.id
    JOIN posts p ON p.id = dsta.answer_post_id
    JOIN group_users_filtered guf ON guf.user_id = p.user_id
    WHERE dsta.created_at BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, DATE_TRUNC(:date_trunc, dsta.created_at)
),
assigns_per_user AS (
    SELECT 
        a.assigned_to_id AS user_id, 
        DATE_TRUNC(:date_trunc, a.created_at) AS month,
        COUNT(a.topic_id) AS total_assigned
    FROM assignments a
    JOIN topics t ON t.id = a.topic_id
    JOIN group_users_filtered guf ON guf.user_id = a.assigned_to_id
    WHERE a.assigned_to_type = 'User'
      AND t.deleted_at IS NULL
      AND a.created_at BETWEEN :start_date AND :end_date
    GROUP BY a.assigned_to_id, DATE_TRUNC(:date_trunc, a.created_at)
)
SELECT 
    COALESCE(qs.month, apu.month)::DATE AS date,
    :date_trunc as date_range,
    COALESCE(qs.user_id, apu.user_id, ug.user_id) AS user_id, 
    COALESCE(qs.total_solved, 0) AS total_solved_questions,
    COALESCE(apu.total_assigned, 0) AS total_assigned_topics,
    COALESCE(ug.group_names, '') AS group_names
FROM questions_solved qs
FULL OUTER JOIN assigns_per_user apu ON qs.user_id = apu.user_id AND qs.month = apu.month
LEFT JOIN user_groups ug ON ug.user_id = COALESCE(qs.user_id, apu.user_id)
WHERE (qs.user_id IN(:user_list) OR :userlist_filter = FALSE)
ORDER BY date DESC

SQL 쿼리 설명

이 보고서는 지정된 매개변수에 기반하여 데이터를 필터링하고 집계하는 일련의 공통 테이블 표현식(CTE)을 통해 생성됩니다. 각 CTE와 보고서에서의 역할을 분해하여 설명하면 다음과 같습니다:

CTE 설명

  1. group_users_filtered: 이 CTE는 지정된 그룹(:group_name_filter)의 구성원인 사용자를 식별합니다. 관심 있는 그룹의 멤버십에 따라 사용자를 필터링합니다.
  2. user_groups: 이 CTE는 사용자가 속한 모든 그룹을 단일 문자열로 집계합니다. 이는 사용자와 관련된 모든 그룹을 식별하는 데 도움이 되어, 해당 사용자의 활동에 대한 맥락을 제공합니다.
  3. questions_solved: 이 CTE는 지정된 날짜 범위(:start_date부터 :end_date까지) 내에 각 사용자가 해결한 질문의 총 수를 계산합니다.
  4. assigns_per_user: questions_solved와 유사하게, 이 CTE는 지정된 날짜 범위 내에 각 사용자에게 할당된 작업의 수를 계산합니다. 사용자에게 할당된 항목만(그룹 또는 기타 엔티티가 아닌) 계산되며 삭제된 주제는 제외되도록 보장합니다.

최종 쿼리는 이러한 CTE를 결합하여 날짜(또는 날짜 범위), 사용자 ID, 해결된 질문 총 수, 할당된 주제 총 수, 그리고 사용자가 속한 그룹의 이름을 포함하는 보고서를 생성합니다. 제공된 경우(:user_list) 특정 사용자 목록으로 필터링할 수 있으며, :date_trunc 매개변수(예: 월, 연도)에 따라 날짜의 세밀도를 조정할 수 있습니다.

매개변수

  • group_name_filter (문자열): 사용자 필터링에 사용할 그룹 이름으로, 기본값은 "staff"입니다. 이 매개변수는 특정 그룹의 구성원인 사용자를 선택하는 데 사용됩니다.
  • date_trunc (문자열): 보고를 위한 시간 집계 세밀도를 결정합니다(예: “month”, “year”, “week”, “day”). 이는 출력에서 데이터가 시간에 따라 어떻게 그룹화되는지에 영향을 미칩니다.
  • user_list (null user_list): 결과를 추가적으로 필터링하기 위한 선택적 사용자 ID 목록입니다. 제공된 경우, 쿼리는 해당 사용자에 대한 데이터만 포함합니다.
  • userlist_filter (부울): user_list 필터를 적용할지 여부를 나타내는 플래그입니다. false로 설정되면 user_list 매개변수는 무시되고, 지정된 그룹의 모든 사용자의 데이터가 포함됩니다.
  • start_date (날짜): 활동을 보고할 기간의 시작 날짜입니다.
  • end_date (날짜): 활동을 보고할 기간의 종료 날짜입니다.

결과

이 보고서는 다음 컬럼을 제공합니다:

  • date: 데이터가 집계된 날짜 또는 날짜 범위의 시작일입니다.
  • date_range: 날짜 범위의 세밀도(예: 월, 연도)입니다.
  • user_id: 사용자의 ID입니다.
  • total_solved_questions: 사용자가 해결한 질문의 총 수입니다.
  • total_assigned_topics: 사용자에게 할당된 작업의 총 수입니다.
  • group_names: 사용자가 속한 모든 그룹의 이름을 포함하는 문자열입니다.

:person_tipping_hand: Discourse의 assignments 테이블이 작동하는 방식 때문에, 이 보고서는 현재 할당된 주제만 추적할 수 있으며, 주제가 할당(또는 재할당)된 날짜 기준으로 현재 할당된 주제를 표시합니다.

결과 예시

date date_range user total_solved_questions total_assigned_topics group_names
2023-12-01 month user1 5 3 admins, customer-projects-team, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
2023-11-01 month user2 8 1 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, support, trust_level_2
2023-11-01 month user3 3 4 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
… … … … … …
2

이 타이밍이 더 좋을 수 없었네요! 바로 이런 걸 하고 싶었는데 보라! 나타났어요! :tada:

감사합니다!

종료된 토픽의 과거 과제 통계는 볼 수 없다는 게 아쉽네요. 다 가질 수는 없겠죠.