Questions résolues et sujets actuellement assignés par utilisateur par mois

Communauté Données et rapports
,
1

:discourse: Ce rapport nécessite que le plugin Discourse Solved soit activé.

Ce rapport Data Explorer offre un aperçu des activités effectuées par les membres d’un groupe spécifique au cours d’une période donnée. Il se concentre plus particulièrement sur deux activités principales : les questions résolues et les sujets attribués.

Ce rapport est conçu pour aider les administrateurs à comprendre les contributions et la charge de travail des membres du groupe, facilitant ainsi une meilleure allocation des ressources et la reconnaissance des contributeurs actifs.

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

Explication de la requête SQL

Le rapport est généré grâce à une série d’expressions de table communes (CTE) qui filtrent et agrègent les données selon les paramètres spécifiés. Voici une analyse de chaque CTE et de son rôle dans le rapport :

Explication des CTE

  1. group_users_filtered : Cette CTE identifie les utilisateurs qui sont membres du groupe spécifié (:group_name_filter). Elle filtre les utilisateurs en fonction de leur appartenance au groupe d’intérêt.
  2. user_groups : Cette CTE regroupe tous les groupes auxquels un utilisateur appartient en une seule chaîne de caractères. Cela permet d’identifier tous les groupes associés à un utilisateur, fournissant ainsi un contexte pour leurs activités.
  3. questions_solved : Cette CTE calcule le nombre total de questions résolues par chaque utilisateur dans la période spécifiée (:start_date à :end_date).
  4. assigns_per_user : Similaire à questions_solved, cette CTE compte le nombre de tâches attribuées à chaque utilisateur dans la période spécifiée. Elle garantit que seules les attributions aux utilisateurs (et non aux groupes ou autres entités) sont comptées et exclut les sujets supprimés.

La requête finale combine ces CTE pour produire un rapport incluant la date (ou la plage de dates), l’ID de l’utilisateur, le nombre total de questions résolues, le nombre total de sujets attribués et les noms des groupes auxquels l’utilisateur appartient. Elle permet de filtrer par une liste spécifique d’utilisateurs si fournie (:user_list) et peut ajuster la granularité des dates selon le paramètre :date_trunc (par exemple, mois, année).

Paramètres

  • group_name_filter (chaîne) : Le nom du groupe utilisé pour filtrer les utilisateurs, défini par défaut sur « staff ». Ce paramètre sert à sélectionner les utilisateurs qui sont membres d’un groupe spécifique.
  • date_trunc (chaîne) : Détermine la granularité de l’agrégation temporelle pour le rapport (par exemple, « mois », « année », « semaine », « jour »). Cela affecte la façon dont les données sont regroupées par temps dans la sortie.
  • user_list (null user_list) : Une liste optionnelle d’IDs d’utilisateurs pour filtrer davantage les résultats. Si fournie, la requête n’inclura que les données pour ces utilisateurs.
  • userlist_filter (booléen) : Un indicateur pour préciser si le filtre user_list doit être appliqué. S’il est défini sur false, le paramètre user_list est ignoré et les données pour tous les utilisateurs du groupe spécifié sont incluses.
  • start_date (date) : La date de début de la période pour laquelle les activités doivent être rapportées.
  • end_date (date) : La date de fin de la période pour laquelle les activités doivent être rapportées.

Résultats

Le rapport fournit les colonnes suivantes :

  • date : La date ou le début de la plage de dates pour laquelle les données sont agrégées.
  • date_range : La granularité de la plage de dates (par exemple, mois, année).
  • user_id : L’ID de l’utilisateur.
  • total_solved_questions : Le nombre total de questions résolues par l’utilisateur.
  • total_assigned_topics : Le nombre total de tâches attribuées à l’utilisateur.
  • group_names : Une chaîne contenant les noms de tous les groupes auxquels l’utilisateur appartient.

:person_tipping_hand: Notez que, en raison du fonctionnement de la table assignments dans Discourse, ce rapport ne pourra suivre que les sujets actuellement attribués et affichera les sujets actuellement attribués selon la date à laquelle ils ont été attribués (ou réattribués).

Exemples de résultats

date date_range user total_solved_questions total_assigned_topics group_names
2023-12-01 mois 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 mois 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 mois user3 3 4 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
… … … … … …
2

Cela n’aurait pas pu mieux tomber ! J’avais justement besoin de faire quelque chose comme ça et VOILÀ ! ça apparaît ! :tada:

Merci !

C’est dommage que nous ne puissions pas obtenir de statistiques sur les devoirs passés (pour les sujets clos). On ne peut pas tout avoir, j’imagine.