This report requires the Discourse Solved plugin to be enabled.
This Data Explorer report provides a overview of the activities performed by members of a specific group within a given time frame. Specifically, it focuses on two main activities: questions solved and topics assigned.
The report is designed to help administrators understand the contributions and workload of group members, facilitating better resource allocation and recognition of active contributors.
--[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
ua.user_id,
DATE_TRUNC(:date_trunc, ua.created_at) AS month,
COUNT(*) AS total_solved
FROM user_actions ua
JOIN group_users_filtered guf ON guf.user_id = ua.user_id
WHERE ua.action_type = 15
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY ua.user_id, DATE_TRUNC(:date_trunc, ua.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 Query Explanation
The report is generated through a series of Common Table Expressions (CTEs) that filter and aggregate data based on the specified parameters. Here’s a breakdown of each CTE and its role in the report:
CTEs Explained
- group_users_filtered: This CTE identifies the users who are members of the specified group (
:group_name_filter). It filters users based on their membership in the group of interest. - user_groups: This CTE aggregates all the groups a user belongs to into a single string. This helps in identifying all the groups associated with a user, providing context for their activities.
- questions_solved: This CTE calculates the total number of questions solved by each user within the specified date range (
:start_dateto:end_date). It filters actions based on the action type that represents solving a question (e.g., action_type = 15) and ensures the user is part of the filtered group. - assigns_per_user: Similar to
questions_solved, this CTE counts the number of tasks assigned to each user in the specified date range. It ensures that only assignments to users (not groups or other entities) are counted and excludes deleted topics.
The final query combines these CTEs to produce a report that includes the date (or date range), user ID, total questions solved, total topics assigned, and the names of the groups the user belongs to. It allows filtering by a specific list of users if provided (:user_list) and can adjust the date granularity based on the :date_trunc parameter (e.g., month, year).
Parameters
group_name_filter(string): The name of the group to filter users by, set to “staff” by default. This parameter is used to select users who are members of a specific group.date_trunc(string): Determines the granularity of time aggregation for reporting (e.g., “month”, “year”, “week”, “day”). This affects how the data is grouped by time in the output.user_list(null user_list): An optional list of user IDs to further filter the results. If provided, the query will only include data for these users.userlist_filter(boolean): A flag to indicate whether theuser_listfilter should be applied. If set tofalse, theuser_listparameter is ignored, and data for all users in the specified group is included.start_date(date): The start date of the period for which to report activities.end_date(date): The end date of the period for which to report activities.
Results
The report provides the following columns:
date: The date or start of the date range for which the data is aggregated.date_range: The granularity of the date range (e.g., month, year).user_id: The ID of the user.total_solved_questions: The total number of questions solved by the user.total_assigned_topics: The total number of tasks assigned to the user.group_names: A string containing the names of all groups the user belongs to.
Note that due to the way that the
assignmentstable in Discourse functions, this report will only be able to track currently assigned topics, and will show currently assigned topics by the date that they were assigned (or re-assigned).
Example Results
| 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 |
| … | … | … | … | … | … |
