Pro Monat gelöste und aktuell zugewiesene Themen pro Benutzer

[SaraDev (Sara Devlaeminck)]

Dieser Bericht erfordert, dass das Discourse Solved -Plugin 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. Konkret konzentriert er sich auf zwei Hauptaktivitäten: gelöste Fragen und zugewiesene Themen.

Der Bericht soll Administratoren dabei unterstützen, die Beiträge und die Arbeitslast der Gruppenmitglieder zu verstehen, was eine bessere Ressourcenallokation und die Anerkennung aktiver Beitragender 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, 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

Erklärung der SQL-Abfrage

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 fasst alle Gruppen, denen ein Benutzer angehört, zu einem einzigen String zusammen. Dies hilft dabei, alle Gruppen zu identifizieren, die mit einem Benutzer verbunden sind, und bietet 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 bei questions_solved zählt diese CTE die Anzahl der Aufgaben, die jedem Benutzer im angegebenen Datumsbereich zugewiesen wurden. Sie stellt sicher, dass nur Zuweisungen an Benutzer (nicht an Gruppen oder andere Entitäten) gezählt werden und gelöschte Themen ausgeschlossen sind.

Die finale 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 Liste von Benutzern, falls angegeben (:user_list), und kann die Datengranularität basierend auf dem Parameter :date_trunc anpassen (z. B. Monat, Jahr).

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 im Output nach Zeit gruppiert werden.
• user_list (null user_list): Eine optionale Liste von Benutzer-IDs, um die Ergebnisse weiter zu filtern. Falls angegeben, enthält die Abfrage nur Daten für diese Benutzer.
• userlist_filter (Boolean): Ein Flag, um anzugeben, ob der user_list-Filter 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 (Datum): Das Startdatum des Zeitraums, für den Aktivitäten berichtet werden sollen.
• end_date (Datum): Das Enddatum des Zeitraums, für den Aktivitäten berichtet werden sollen.

Ergebnisse

Der Bericht bietet folgende 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: Ein String, der die Namen aller Gruppen enthält, denen der Benutzer angehört.

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

Beispiel-Ergebnisse

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.