Questions Solved and Currently Assigned Topics by User per Month

:discourse: 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

  1. 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.
  2. 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.
  3. questions_solved: This CTE calculates the total number of questions solved by each user within the specified date range (:start_date to :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.
  4. 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 the user_list filter should be applied. If set to false, the user_list parameter 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.

:person_tipping_hand: Note that due to the way that the assignments table 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
2 Likes

This could not have been better timed! I was just needing to do something like this and VOILA! it appeareth! :tada:

Thanks!

It is a shame we can’t get stats on assignments in the past (for closed topics). Can’t have everything I guess.

1 Like