Perguntas Resolvidas e Tópicos Atualmente Atribuídos por Usuário por Mês

Dados e relatórios
,
1

:discourse: Este relatório requer que o plugin Discourse Solved esteja ativado.

Este relatório do Data Explorer fornece uma visão geral das atividades realizadas por membros de um grupo específico em um determinado período de tempo. Especificamente, ele se concentra em duas atividades principais: perguntas resolvidas e tópicos atribuídos.
O relatório foi projetado para ajudar os administradores a entender as contribuições e a carga de trabalho dos membros do grupo, facilitando uma melhor alocação de recursos e o reconhecimento de contribuintes ativos.

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

Explicação da Consulta SQL

O relatório é gerado através de uma série de Expressões de Tabela Comuns (CTEs) que filtram e agregam dados com base nos parâmetros especificados. Aqui está uma análise de cada CTE e seu papel no relatório:

CTEs Explicadas

  1. group_users_filtered: Esta CTE identifica os usuários que são membros do grupo especificado (:group_name_filter). Ela filtra os usuários com base em sua associação ao grupo de interesse.
  2. user_groups: Esta CTE agrega todos os grupos aos quais um usuário pertence em uma única string. Isso ajuda a identificar todos os grupos associados a um usuário, fornecendo contexto para suas atividades.
  3. questions_solved: Esta CTE calcula o número total de perguntas resolvidas por cada usuário dentro do intervalo de datas especificado (:start_date a :end_date).
  4. assigns_per_user: Semelhante a questions_solved, esta CTE conta o número de tarefas atribuídas a cada usuário no intervalo de datas especificado. Ela garante que apenas atribuições a usuários (não a grupos ou outras entidades) sejam contadas e exclui tópicos excluídos.

A consulta final combina essas CTEs para produzir um relatório que inclui a data (ou intervalo de datas), ID do usuário, total de perguntas resolvidas, total de tópicos atribuídos e os nomes dos grupos aos quais o usuário pertence. Ela permite filtrar por uma lista específica de usuários, se fornecida (:user_list), e pode ajustar a granularidade da data com base no parâmetro :date_trunc (por exemplo, mês, ano).

Parâmetros

  • group_name_filter (string): O nome do grupo para filtrar usuários, definido como “staff” por padrão. Este parâmetro é usado para selecionar usuários que são membros de um grupo específico.
  • date_trunc (string): Determina a granularidade da agregação de tempo para relatórios (por exemplo, “month”, “year”, “week”, “day”). Isso afeta como os dados são agrupados por tempo na saída.
  • user_list (null user_list): Uma lista opcional de IDs de usuário para filtrar ainda mais os resultados. Se fornecida, a consulta incluirá apenas dados para esses usuários.
  • userlist_filter (boolean): Um sinalizador para indicar se o filtro user_list deve ser aplicado. Se definido como false, o parâmetro user_list é ignorado e dados de todos os usuários no grupo especificado são incluídos.
  • start_date (date): A data de início do período para o qual relatar atividades.
  • end_date (date): A data de término do período para o qual relatar atividades.

Resultados

O relatório fornece as seguintes colunas:

  • date: A data ou o início do intervalo de datas para o qual os dados são agregados.
  • date_range: A granularidade do intervalo de datas (por exemplo, mês, ano).
  • user_id: O ID do usuário.
  • total_solved_questions: O número total de perguntas resolvidas pelo usuário.
  • total_assigned_topics: O número total de tarefas atribuídas ao usuário.
  • group_names: Uma string contendo os nomes de todos os grupos aos quais o usuário pertence.

:person_tipping_hand: Observe que, devido à forma como a tabela assignments no Discourse funciona, este relatório só será capaz de rastrear tópicos atualmente atribuídos e mostrará os tópicos atualmente atribuídos pela data em que foram atribuídos (ou reatribuídos).

Exemplo de Resultados

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 curtidas
2

Isso não poderia ter vindo em melhor hora! Eu estava precisando fazer algo assim e VOILÀ! apareceu! :tada:

Obrigado!

É uma pena que não possamos obter estatísticas sobre tarefas passadas (para tópicos fechados). Não se pode ter tudo, eu acho.

1 curtida