Preguntas resueltas y temas asignados actualmente por usuario por mes

Comunidad Datos e informes
,
1

:discourse: Este informe requiere que el plugin Discourse Solved esté habilitado.

Este informe de Data Explorer ofrece una visión general de las actividades realizadas por los miembros de un grupo específico dentro de un período determinado. En concreto, se centra en dos actividades principales: preguntas resueltas y temas asignados.

El informe está diseñado para ayudar a los administradores a comprender las contribuciones y la carga de trabajo de los miembros del grupo, facilitando una mejor asignación de recursos y el reconocimiento de los colaboradores activos.

--[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

Explicación de la consulta SQL

El informe se genera mediante una serie de Expresiones de Tabla Común (CTE) que filtran y agregan datos según los parámetros especificados. A continuación, se detalla cada CTE y su función en el informe:

Explicación de las CTE

  1. group_users_filtered: Esta CTE identifica a los usuarios que son miembros del grupo especificado (:group_name_filter). Filtra a los usuarios en función de su pertenencia al grupo de interés.
  2. user_groups: Esta CTE agrupa todos los grupos a los que pertenece un usuario en una sola cadena. Esto ayuda a identificar todos los grupos asociados a un usuario, proporcionando contexto para sus actividades.
  3. questions_solved: Esta CTE calcula el número total de preguntas resueltas por cada usuario dentro del rango de fechas especificado (:start_date a :end_date).
  4. assigns_per_user: Al igual que questions_solved, esta CTE cuenta el número de tareas asignadas a cada usuario en el rango de fechas especificado. Asegura que solo se cuenten las asignaciones a usuarios (no a grupos u otras entidades) y excluye los temas eliminados.

La consulta final combina estas CTE para generar un informe que incluye la fecha (o el rango de fechas), el ID de usuario, el total de preguntas resueltas, el total de temas asignados y los nombres de los grupos a los que pertenece el usuario. Permite filtrar por una lista específica de usuarios si se proporciona (:user_list) y puede ajustar la granularidad de las fechas según el parámetro :date_trunc (por ejemplo, mes, año).

Parámetros

  • group_name_filter (cadena): El nombre del grupo por el que filtrar a los usuarios, establecido en „staff“ de forma predeterminada. Este parámetro se utiliza para seleccionar a los usuarios que son miembros de un grupo específico.
  • date_trunc (cadena): Determina la granularidad de la agregación temporal para los informes (por ejemplo, „month“, „year“, „week“, „day“). Esto afecta a cómo se agrupan los datos por tiempo en la salida.
  • user_list (null user_list): Una lista opcional de IDs de usuario para filtrar aún más los resultados. Si se proporciona, la consulta solo incluirá datos para estos usuarios.
  • userlist_filter (booleano): Un indicador para especificar si se debe aplicar el filtro user_list. Si se establece en false, el parámetro user_list se ignora y se incluyen los datos de todos los usuarios del grupo especificado.
  • start_date (fecha): La fecha de inicio del período para el que se reportan las actividades.
  • end_date (fecha): La fecha de fin del período para el que se reportan las actividades.

Resultados

El informe proporciona las siguientes columnas:

  • date: La fecha o el inicio del rango de fechas para el que se agregan los datos.
  • date_range: La granularidad del rango de fechas (por ejemplo, mes, año).
  • user_id: El ID del usuario.
  • total_solved_questions: El número total de preguntas resueltas por el usuario.
  • total_assigned_topics: El número total de tareas asignadas al usuario.
  • group_names: Una cadena que contiene los nombres de todos los grupos a los que pertenece el usuario.

:person_tipping_hand: Tenga en cuenta que, debido a la forma en que funciona la tabla assignments en Discourse, este informe solo podrá rastrear los temas asignados actualmente y mostrará los temas asignados actualmente según la fecha en que se asignaron (o reasignaron).

Resultados de ejemplo

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

¡Esto no podría haber llegado en mejor momento! Justo necesitaba hacer algo así y ¡VOILÀ! aparece. :tada:

¡Gracias!

Es una pena que no podamos obtener estadísticas de las asignaciones pasadas (para temas cerrados). Supongo que no se puede tener todo.