Gelöste Fragen und aktuell zugewiesene Themen pro Benutzer pro Monat

[SaraDev (Sara Devlaeminck)]

Dieser Bericht erfordert, dass das Plugin Discourse Solved aktiviert ist.

Dieser Data Explorer-Bericht bietet einen Überblick über die Aktivitäten, die von Mitgliedern einer bestimmten Gruppe innerhalb eines bestimmten Zeitraums durchgeführt wurden. Insbesondere konzentriert er sich auf zwei Hauptaktivitäten: gelöste Fragen und zugewiesene Themen.

Der Bericht soll Administratoren helfen, die Beiträge und die Arbeitsbelastung der Gruppenmitglieder zu verstehen, was eine bessere Ressourcenzuweisung und die Anerkennung aktiver Mitwirkender erleichtert.

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

SQL-Abfrageerklärung

Der Bericht wird durch eine Reihe von Common Table Expressions (CTEs) generiert, die Daten basierend auf den angegebenen Parametern filtern und aggregieren. Hier ist eine Aufschlüsselung jeder CTE und ihrer Rolle im Bericht:

CTEs erklärt

1. group_users_filtered: Diese CTE identifiziert die Benutzer, die Mitglieder der angegebenen Gruppe (:group_name_filter) sind. Sie filtert Benutzer basierend auf ihrer Mitgliedschaft in der interessierenden Gruppe.
2. user_groups: Diese CTE aggregiert alle Gruppen, zu denen ein Benutzer gehört, in einer einzigen Zeichenkette. Dies hilft bei der Identifizierung aller mit einem Benutzer verbundenen Gruppen und liefert Kontext für seine Aktivitäten.
3. questions_solved: Diese CTE berechnet die Gesamtzahl der von jedem Benutzer innerhalb des angegebenen Datumsbereichs (:start_date bis :end_date) gelösten Fragen.
4. assigns_per_user: Ähnlich wie questions_solved zählt diese CTE die Anzahl der jedem Benutzer im angegebenen Datumsbereich zugewiesenen Aufgaben. Sie stellt sicher, dass nur Zuweisungen an Benutzer (nicht an Gruppen oder andere Entitäten) gezählt werden und schließt gelöschte Themen aus.

Die endgültige Abfrage kombiniert diese CTEs, um einen Bericht zu erstellen, der das Datum (oder den Datumsbereich), die Benutzer-ID, die insgesamt gelösten Fragen, die insgesamt zugewiesenen Themen und die Namen der Gruppen, denen der Benutzer angehört, enthält. Sie ermöglicht die Filterung nach einer bestimmten Benutzerliste, falls vorhanden (:user_list), und kann die Granularität der Daten basierend auf dem Parameter :date_trunc (z. B. Monat, Jahr) anpassen.

Parameter

• group_name_filter (string): Der Name der Gruppe, nach der Benutzer gefiltert werden sollen, standardmäßig auf „staff“ gesetzt. Dieser Parameter wird verwendet, um Benutzer auszuwählen, die Mitglieder einer bestimmten Gruppe sind.
• date_trunc (string): Bestimmt die Granularität der Zeitaggregation für die Berichterstattung (z. B. „month“, „year“, „week“, „day“). Dies beeinflusst, wie die Daten in der Ausgabe nach Zeit gruppiert werden.
• user_list (null user_list): Eine optionale Liste von Benutzer-IDs zur weiteren Filterung der Ergebnisse. Wenn angegeben, enthält die Abfrage nur Daten für diese Benutzer.
• userlist_filter (boolean): Ein Flag, das angibt, ob der Filter user_list angewendet werden soll. Wenn auf false gesetzt, wird der Parameter user_list ignoriert und Daten für alle Benutzer in der angegebenen Gruppe werden einbezogen.
• start_date (date): Das Startdatum des Zeitraums, für den Aktivitäten gemeldet werden sollen.
• end_date (date): Das Enddatum des Zeitraums, für den Aktivitäten gemeldet werden sollen.

Ergebnisse

Der Bericht enthält die folgenden Spalten:

• date: Das Datum oder der Beginn des Datumsbereichs, für den die Daten aggregiert werden.
• date_range: Die Granularität des Datumsbereichs (z. B. Monat, Jahr).
• user_id: Die ID des Benutzers.
• total_solved_questions: Die Gesamtzahl der vom Benutzer gelösten Fragen.
• total_assigned_topics: Die Gesamtzahl der dem Benutzer zugewiesenen Aufgaben.
• group_names: Eine Zeichenkette mit den Namen aller Gruppen, denen der Benutzer angehört.

Beachten Sie, dass dieser Bericht aufgrund der Funktionsweise der assignments-Tabelle in Discourse nur derzeit zugewiesene Themen verfolgen kann und derzeit zugewiesene Themen nach dem Datum anzeigt, an dem sie zugewiesen (oder neu zugewiesen) wurden.

Beispielergebnisse

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

[aidanheerdegen]

Das hätte nicht besser getimt sein können! Ich musste gerade etwas Ähnliches tun und VOILA! es erscheint!

Danke!

Es ist schade, dass wir keine Statistiken für vergangene Aufgaben (für geschlossene Themen) erhalten können. Man kann nicht alles haben, nehme ich an.