Dies ist eine SQL-Version des Berichts „Flaggenstatus“.
Der Bericht „Flaggenstatus“ soll Einblicke in den Moderationsprozess geben, indem die Zeit analysiert wird, die zur Behebung von markierten Beiträgen innerhalb eines bestimmten Zeitraums benötigt wird. Er enthält Details zur Art der erhobenen Markierung, den beteiligten Beiträgen und Benutzern, dem Erstellungsdatum der Markierung, dem Lösungsstatus, dem Mitarbeiter, der die Markierung bearbeitet hat, und der Zeit, die bis zur Lösung benötigt wurde.
Dieser Bericht ist für Community-Manager und Moderatoren nützlich, um die Effizienz des Moderationsteams zu bewerten, Probleme im Lösungsprozess zu identifizieren und sicherzustellen, dass die Community-Standards zeitnah eingehalten werden.
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
WITH period_actions AS (
SELECT pa.id,
pa.post_action_type_id,
pa.created_at,
pa.agreed_at,
pa.disagreed_at,
pa.deferred_at,
pa.agreed_by_id,
pa.disagreed_by_id,
pa.deferred_by_id,
pa.post_id,
pa.user_id,
COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- Zeit bis zur Lösung in Minuten
FROM post_actions pa
WHERE pa.post_action_type_id IN (3,4,6,7,8)
AND pa.created_at >= :start_date
AND pa.created_at <= :end_date
),
poster_data AS (
SELECT pa.id,
p.user_id AS poster_id,
p.topic_id,
p.post_number,
u.username_lower AS poster_username,
u.uploaded_avatar_id AS poster_avatar_id
FROM period_actions pa
JOIN posts p ON p.id = pa.post_id
JOIN users u ON u.id = p.user_id
),
flagger_data AS (
SELECT pa.id,
u.id AS flagger_id,
u.username_lower AS flagger_username,
u.uploaded_avatar_id AS flagger_avatar_id
FROM period_actions pa
JOIN users u ON u.id = pa.user_id
),
staff_data AS (
SELECT pa.id,
u.id AS staff_id,
u.username_lower AS staff_username,
u.uploaded_avatar_id AS staff_avatar_id
FROM period_actions pa
JOIN users u ON u.id = COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id)
),
flag_types AS (
SELECT pat.id,
pat.id AS flag_type
FROM post_action_types pat
)
SELECT
CASE
WHEN pat.flag_type = 3 THEN 'off_topic'
WHEN pat.flag_type = 4 THEN 'inappropriate'
WHEN pat.flag_type = 6 THEN 'notify_user'
WHEN pat.flag_type = 7 THEN 'notify_moderators'
WHEN pat.flag_type = 8 THEN 'spam'
END "type",
pa.post_id as flagged_post_id,
pd.poster_id as poster_user_id,
fd.flagger_id as flagger_user_id,
DATE(pa.created_at) as flag_created,
CASE
WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
END "resolution",
sd.staff_id as assigned_user_id,
ROUND(pa.time_to_resolution_minutes,2) as "resolution_time (minutes)"
FROM period_actions pa
JOIN poster_data pd ON pd.id = pa.id
JOIN flagger_data fd ON fd.id = pa.id
LEFT JOIN staff_data sd ON sd.id = pa.id
JOIN flag_types pat ON pat.id = pa.post_action_type_id
ORDER BY pa.created_at ASC
SQL-Abfrageerklärung
Die Abfrage ist mithilfe von Common Table Expressions (CTEs) strukturiert, die eine Teilmenge von Daten vorbereiten, die dann in der endgültigen SELECT-Anweisung zur Zusammenstellung des Berichts verwendet wird.
Parameter
Die Abfrage akzeptiert zwei Parameter:
:start_date: Der Beginn des Zeitraums, für den der Bericht erstellt werden soll.:end_date: Das Ende des Zeitraums, für den der Bericht erstellt werden soll.
CTEs
period_actions
Diese CTE wählt Post-Aktionen (Flags) aus, die in den angegebenen Datumsbereich fallen und bestimmte Typen (3, 4, 6, 7, 8) aufweisen, die verschiedenen Flag-Gründen entsprechen. Sie berechnet die „time_to_resolution_minutes“, indem sie die Differenz zwischen der Erstellungszeit des Flags und der Zeit, zu der darauf reagiert wurde (zugestimmt, abgelehnt oder zurückgestellt), ermittelt.
poster_data
Diese CTE verknüpft period_actions mit den Tabellen posts und users, um Informationen über den Ersteller des markierten Beitrags abzurufen, einschließlich seiner Benutzer-ID, Topic-ID, Beitragsnummer und seines Benutzernamens.
flagger_data
Diese CTE verknüpft period_actions mit der Tabelle users, um Informationen über den Benutzer zu erhalten, der den Beitrag markiert hat, einschließlich seiner Benutzer-ID und seines Benutzernamens.
staff_data
Diese CTE ruft Informationen über den Mitarbeiter ab, der das Flag bearbeitet hat, indem sie period_actions mit der Tabelle users basierend auf den IDs der Mitarbeiter verknüpft, die das Flag genehmigt, abgelehnt oder zurückgestellt haben.
flag_types
Diese CTE wählt einfach die IDs aus der Tabelle post_action_types aus, die zur Bestimmung des Flag-Typs in der endgültigen SELECT-Anweisung verwendet werden.
Finale SELECT
Die endgültige SELECT-Anweisung kombiniert alle CTEs, um einen umfassenden Bericht zu präsentieren. Sie enthält die folgenden Spalten:
type: Die Art des erhobenen Flags, bestimmt durch die IDflag_type.flagged_post_id: Die ID des markierten Beitrags.poster_user_id: Die ID des Benutzers, der den markierten Inhalt gepostet hat.flagger_user_id: Die ID des Benutzers, der den Inhalt markiert hat.flag_created: Das Datum, an dem das Flag erstellt wurde.resolution: Der Lösungsstatus des Flags (zugestimmt, abgelehnt, zurückgestellt).assigned_user_id: Die ID des Mitarbeiters, der das Flag bearbeitet hat.resolution_time (minutes): Die Zeit bis zur Lösung des Flags in Minuten, gerundet auf zwei Dezimalstellen.
Der Bericht wird dann nach dem Erstellungsdatum des Flags (pa.created_at) aufsteigend sortiert.
Beispielergebnisse
| type | flagged_post | poster_user | flagger_user | flag_created | resolution | assigned_user | resolution_time (minutes) |
|---|---|---|---|---|---|---|---|
| off_topic | Example_Post_Link1 | example_user1 | example_user2 | 2023-01-01 | agreed | example_user3 | 4.56 |
| inappropriate | Example_Post_Link2 | example_user1 | example_user2 | 2023-01-02 | disagreed | example_user3 | 38.76 |
| spam | Example_Post_Link3 | example_user1 | example_user2 | 2023-01-03 | deferred | example_user3 | 79.32 |
| — | — | — | — | — | — | — | — |