# 사용자별 월별 해결된 질문 및 현재 할당된 주제
**URL:** https://meta.discourse.org/t/questions-solved-and-currently-assigned-topics-by-user-per-month/301567
**Category:** Data & reporting
**Tags:** solved, sql-query
**Created:** [3월 29, 2024, 9:05오후 UTC](https://meta.discourse.org/t/questions-solved-and-currently-assigned-topics-by-user-per-month/301567 "2024-03-29T21:05:16Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [3월 29, 2024, 9:05오후 UTC](https://meta.discourse.org/t/questions-solved-and-currently-assigned-topics-by-user-per-month/301567/1 "2024-03-29T21:05:17Z")
> :discourse: 이 보고서를 사용하려면 [Discourse Solved](https://meta.discourse.org/t/discourse-solved/30155) 플러그인이 활성화되어 있어야 합니다.
이 [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 보고서는 특정 기간 내에 특정 그룹의 구성원들이 수행한 활동에 대한 개요를 제공합니다. 구체적으로, 해결된 질문과 할당된 주제라는 두 가지 주요 활동에 초점을 맞춥니다.
이 보고서는 관리자가 그룹 구성원들의 기여도와 업무량을 이해할 수 있도록 설계되어, 더 나은 자원 배분과 적극적인 기여자에 대한 인정을 촉진합니다.
```sql
--[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`: 사용자가 속한 모든 그룹의 이름을 포함하는 문자열입니다.
> 💁 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 |
| … | … | … | … | … | … |
---
### Author:  [@aidanheerdegen](https://meta.discourse.org/u/aidanheerdegen)
#### Post date: [4월 6, 2024, 11:17오전 UTC](https://meta.discourse.org/t/questions-solved-and-currently-assigned-topics-by-user-per-month/301567/2 "2024-04-06T11:17:40Z")
이 타이밍이 더 좋을 수 없었네요! 바로 이런 걸 하고 싶었는데 **보라!** 나타났어요! 🎉
감사합니다!
종료된 토픽의 과거 과제 통계는 볼 수 없다는 게 아쉽네요. 다 가질 수는 없겠죠.