Dies ist eine SQL-Version des Dashboard-Berichts für Moderatoraktivitäten.
Dieser Bericht bietet einen umfassenden Überblick über die Aktivitäten von Moderatoren innerhalb eines bestimmten Zeitraums auf einer Discourse-Website. Der Bericht kombiniert mehrere Aspekte der Moderatoraktivität: auf der Plattform verbrachte Zeit, Anzahl der geprüften Flags, erstellte Beiträge, erstellte persönliche Nachrichten (PMs), erstellte Themen und vorgenommene Beitragsrevisionen.
Dieser Dashboard-Bericht ist ein wertvolles Werkzeug für Administratoren, die die Effektivität und das Engagement ihres Moderationsteams messen möchten. Er bietet einen detaillierten Einblick in ihre Aktivitäten und Beiträge. Die durch den Bericht gewonnenen Erkenntnisse können Entscheidungen über Schulungen, Anerkennung und Rekrutierung von Moderatoren informieren und sicherstellen, dass das Moderationsteam gut ausbalanciert und effektiv bei der Aufrechterhaltung der Community-Standards ist.
--[params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false
WITH mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
AND u.id > 0
),
time_read AS (
SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv ON m.user_id = uv.user_id
WHERE uv.visited_at >= :start_date
AND uv.visited_at <= :end_date
GROUP BY uv.user_id
),
flag_count AS (
WITH period_actions AS (
SELECT agreed_by_id,
disagreed_by_id
FROM post_actions
WHERE post_action_type_id IN (3,4,8,6,7)
AND created_at >= :start_date
AND created_at <= :end_date
),
agreed_flags AS (
SELECT pa.agreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.agreed_by_id = m.user_id
GROUP BY agreed_by_id
),
disagreed_flags AS (
SELECT pa.disagreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.disagreed_by_id = m.user_id
GROUP BY disagreed_by_id
)
SELECT
COALESCE(af.user_id, df.user_id) AS user_id,
COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
FROM agreed_flags af
FULL OUTER JOIN disagreed_flags df
ON df.user_id = af.user_id
),
revision_count AS (
SELECT pr.user_id,
COUNT(*) AS revision_count
FROM mods m
JOIN post_revisions pr
ON pr.user_id = m.user_id
JOIN posts p
ON p.id = pr.post_id
WHERE pr.created_at >= :start_date
AND pr.created_at <= :end_date
AND p.user_id <> pr.user_id
GROUP BY pr.user_id
),
topic_count AS (
SELECT t.user_id,
COUNT(*) AS topic_count
FROM mods m
JOIN topics t ON t.user_id = m.user_id
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY t.user_id
),
post_count AS (
SELECT p.user_id,
COUNT(*) AS post_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
),
pm_count AS (
SELECT p.user_id,
COUNT(*) AS pm_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
)
SELECT
m.user_id,
m.username,
fc.flag_count as flags_reviewed,
ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
tc.topic_count as topics_created,
pmc.pm_count as PMs_created,
pc.post_count as posts_created,
rc.revision_count as revisions
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
ORDER BY m.username ASC
Parameter
:start_dateund:end_date- Diese Parameter definieren den Datumsbereich für den Bericht. Beide Datumsparameter akzeptieren das DatumsformatYYYY-MM-DD.:include_admins- Dieser Parameter bestimmt, ob Administratoren zusammen mit Moderatoren in den Bericht aufgenommen werden sollen.
SQL-Abfrageerklärung
Der Bericht ist mit Common Table Expressions (CTEs) strukturiert, um die Datenverarbeitung in überschaubare und logische Abschnitte zu unterteilen. Hier ist, was in jeder CTE geschieht:
- mods: Identifiziert alle Benutzer mit Moderatorstatus oder Administratorstatus (falls durch den Parameter
:include_adminseingeschlossen). Es werden nur relevante Benutzerdaten für weitere Abfragen ausgewählt. - time_read: Berechnet die Gesamtzeit (in Sekunden), die jeder Moderator zwischen den angegebenen Start- und Enddaten mit dem Lesen von Inhalten auf der Plattform verbracht hat.
- flag_count: Zählt die Anzahl der Flags, denen Moderatoren im angegebenen Zeitraum zugestimmt oder widersprochen haben. Es werden mehrere Flag-Typen berücksichtigt, die durch ihre jeweiligen Post-Aktionstyp-IDs dargestellt werden.
- revision_count: Zählt die Anzahl der Beitragsrevisionen, die von Moderatoren an Beiträgen anderer Benutzer im angegebenen Zeitraum vorgenommen wurden.
- topic_count: Zählt die Anzahl der von Moderatoren erstellten regulären Themen.
- post_count: Zählt die Anzahl der von Moderatoren in regulären Themen erstellten Beiträge.
- pm_count: Zählt die Anzahl der von Moderatoren initiierten privaten Nachrichten.
Nachdem die Daten in den CTEs gesammelt wurden, verknüpft die Hauptabfrage sie anhand der Benutzer-ID und stellt den endgültigen Bericht zusammen, der den Benutzernamen jedes Moderators, die Gesamtlesezeit (in Stunden umgerechnet), die Anzahl der geprüften Flags, erstellten Themen, erstellten persönlichen Nachrichten, erstellten Beiträge und vorgenommenen Revisionen anzeigt. Die Ergebnisse werden alphabetisch nach dem Benutzernamen des Moderators sortiert.
Beispielergebnisse
| user | username | flags_reviewed | time_reading_hours | topics_created | pms_created | posts_created | revisions |
|---|---|---|---|---|---|---|---|
| 1 | moderator1 | NULL | 36.11 | NULL | 344 | 8 | 15 |
| 2 | moderator2 | 46 | 104.52 | 2 | 271 | 466 | 363 |
| 3 | moderator3 | NULL | 72.15 | NULL | 418 | 64 | 16 |
