Preguntas Resueltas y Temas Asignados Actualmente por Usuario por Mes

Datos e informes
,
1

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

Este informe de Data Explorer proporciona una visión general de las actividades realizadas por los miembros de un grupo específico dentro de un período de tiempo determinado. Específicamente, se enfoca 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 contribuyentes 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, dst.created_at) AS month,
        COUNT(*) AS total_solved
    FROM discourse_solved_solved_topics dst
    JOIN posts p ON p.id = dst.answer_post_id
    JOIN group_users_filtered guf ON guf.user_id = p.user_id
    WHERE dst.created_at BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, DATE_TRUNC(:date_trunc, dst.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 a través de una serie de Expresiones Comunes de Tabla (CTE) que filtran y agregan datos según los parámetros especificados. Aquí hay un desglose de cada CTE y su función en el informe:

CTEs Explicadas

  1. group_users_filtered: Esta CTE identifica a los usuarios que son miembros del grupo especificado (:group_name_filter). Filtra a los usuarios según su membresía en el grupo de interés.
  2. user_groups: Esta CTE agrega todos los grupos a los que pertenece un usuario en una sola cadena. Esto ayuda a identificar todos los grupos asociados con 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: Similar a 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 producir un informe que incluye la fecha (o rango de fechas), el ID de usuario, las preguntas totales resueltas, los temas totales 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 la fecha según el parámetro :date_trunc (por ejemplo, mes, año).

Parámetros

  • group_name_filter (string): El nombre del grupo para filtrar usuarios, establecido en “staff” por defecto. Este parámetro se utiliza para seleccionar usuarios que son miembros de un grupo específico.
  • date_trunc (string): Determina la granularidad de la agregación de tiempo para la generación de informes (por ejemplo, “month”, “year”, “week”, “day”). Esto afecta 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 (boolean): Una bandera para indicar si se debe aplicar el filtro user_list. Si se establece en false, el parámetro user_list se ignora y se incluyen datos para todos los usuarios del grupo especificado.
  • start_date (date): La fecha de inicio del período para el cual se informan las actividades.
  • end_date (date): La fecha de finalización del período para el cual se informan las actividades.

Resultados

El informe proporciona las siguientes columnas:

  • date: La fecha o el inicio del rango de fechas para el cual 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 por la fecha en que fueron asignados (o reasignados).

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
… … … … … …
3 Me gusta
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.

1 me gusta