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

Données et rapports
,
1

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

Ce rapport Data Explorer fournit un aperçu des activités effectuées par les membres d’un groupe spécifique au cours d’une période donnée. Plus précisément, il se concentre sur deux activités principales : les questions résolues et les sujets assignés.
Le 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, 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

Explication de la requête SQL

Le rapport est généré via une série d’expressions de table communes (CTE) qui filtrent et agrègent les données en fonction des paramètres spécifiés. Voici une ventilation de chaque CTE et de son rôle dans le rapport :

CTE expliqués

  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 agrège tous les groupes auxquels un utilisateur appartient en une seule chaîne. Cela permet d’identifier tous les groupes associés à un utilisateur, fournissant ainsi un contexte à ses activités.
  3. questions_solved : Cette CTE calcule le nombre total de questions résolues par chaque utilisateur dans la plage de dates spécifiée (:start_date à :end_date).
  4. assigns_per_user : Similaire à questions_solved, cette CTE compte le nombre de tâches assignées à chaque utilisateur dans la plage de dates spécifiée. Elle garantit que seules les assignations 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 qui comprend la date (ou la plage de dates), l’ID utilisateur, le total des questions résolues, le total des sujets assignés et les noms des groupes auxquels l’utilisateur appartient. Elle permet de filtrer par une liste spécifique d’utilisateurs si elle est fournie (:user_list) et peut ajuster la granularité de la date en fonction du paramètre :date_trunc (par exemple, mois, année).

Paramètres

  • group_name_filter (string) : Le nom du groupe utilisé pour filtrer les utilisateurs, défini par défaut sur « staff ». Ce paramètre est utilisé pour sélectionner les utilisateurs qui sont membres d’un groupe spécifique.
  • date_trunc (string) : Détermine la granularité de l’agrégation temporelle pour le reporting (par exemple, « month », « year », « week », « day »). Cela affecte la manière dont les données sont regroupées par temps dans la sortie.
  • user_list (null user_list) : Une liste facultative d’identifiants d’utilisateurs pour filtrer davantage les résultats. Si elle est fournie, la requête n’inclura que les données de ces utilisateurs.
  • userlist_filter (boolean) : Un indicateur pour déterminer 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 de 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’identifiant 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 assignées à l’utilisateur.
  • group_names : Une chaîne contenant les noms de tous les groupes auxquels l’utilisateur appartient.

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

Exemple de résultats

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 « J'aime »
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.

1 « J'aime »