Dashboard-Bericht - Flags

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für Flags

Dieser Bericht liefert eine Zählung von markierten Beiträgen innerhalb einer optionalen spezifischen Kategorie (und optional ihrer Unterkategorien) über einen bestimmten Datumsbereich. Diese Informationen können für Community-Manager und Moderatoren nützlich sein, um das Volumen der in bestimmten Forenbereichen erhobenen Flags zu verstehen, was bei der Identifizierung von Trends und Bereichen helfen kann, die zusätzliche Aufmerksamkeit oder Moderation erfordern.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
-- null int :category_id = 0
-- boolean :include_subcategories = false

WITH RECURSIVE subcategories AS (
    SELECT id FROM categories WHERE id = :category_id
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.id
)

SELECT
    DATE(r.created_at) AS flag_date,
    COUNT(r.id) AS flags_count
FROM reviewables r
LEFT JOIN categories c ON r.category_id = c.id
WHERE r.type = 'ReviewableFlaggedPost'
    AND r.created_at::DATE BETWEEN :start_date AND :end_date
    AND (:category_id = 0 OR (
        :include_subcategories
        AND r.category_id IN (SELECT id FROM subcategories)
        OR NOT :include_subcategories
        AND r.category_id = :category_id
    ))
GROUP BY flag_date
ORDER BY flag_date

Erklärung der SQL-Abfrage

Die SQL-Abfrage ist so konzipiert, dass sie die Anzahl der Flags (die in der Tabelle reviewables als ReviewableFlaggedPost-Datensätze dargestellt werden), die an jedem Datum innerhalb des angegebenen Datumsbereichs und der Kategorie erstellt wurden, zählt. Sie verwendet eine rekursive Common Table Expression (CTE), um optional Unterkategorien der ausgewählten Kategorie einzuschließen.

Parameter

  • :start_date: Der Beginn des Datumsbereichs, für den Flags gezählt werden sollen.
  • :end_date: Das Ende des Datumsbereichs, für den Flags gezählt werden sollen.
  • :category_id: Die ID der Kategorie, für die Flags gezählt werden sollen. Wenn auf 0 gesetzt, wird der Filter nach Kategorie nicht angewendet.
  • :include_subcategories: Ein boolescher Wert, der bestimmt, ob Unterkategorien der angegebenen Kategorie in die Zählung einbezogen werden sollen.

Abfrageaufschlüsselung

  1. Rekursive CTE (subcategories):
    • Diese CTE beginnt mit der Auswahl der Kategorie mit der ID, die vom Parameter :category_id bereitgestellt wird.
    • Sie wählt dann rekursiv alle Unterkategorien dieser Kategorie aus, indem sie die Tabelle categories mit der CTE subcategories über das Feld parent_category_id verknüpft.
    • Das Ergebnis ist eine Liste aller Unterkategorien (einschließlich der ursprünglichen Kategorie), die für die Filterung verwendet wird.
  2. Hauptabfrage:
    • Die Abfrage wählt zwei Spalten aus: das Datum der Flag-Erstellung (flag_date) und die Anzahl der Flags (flags_count) für dieses Datum.
    • Sie verknüpft die Tabelle reviewables mit der Tabelle categories, um jedes Flag seiner Kategorie zuzuordnen.
  3. Filter:
    • Die Abfrage filtert reviewables-Datensätze, um nur diejenigen vom Typ ReviewableFlaggedPost einzuschließen, was markierte Beiträge darstellt.
    • Sie stellt sicher, dass das Erstellungsdatum (created_at) der Flags innerhalb des angegebenen Datumsbereichs (:start_date bis :end_date) liegt.
    • Sie wendet einen bedingten Filter basierend auf den Parametern :category_id und :include_subcategories an:
      • Wenn :category_id 0 ist, wird kein Kategorie-Filter angewendet.
      • Wenn :include_subcategories true ist, schließt die Abfrage Flags aus der ursprünglichen Kategorie und allen ihren Unterkategorien ein.
      • Wenn :include_subcategories false ist, schließt die Abfrage nur Flags aus der ursprünglichen Kategorie ein.
  4. Gruppierung und Sortierung:
    • Die Ergebnisse werden nach dem Datum der Flag-Erstellung gruppiert, um eine tägliche Zählung zu erhalten.
    • Die Ergebnisse werden nach Datum sortiert, um die Daten in chronologischer Reihenfolge darzustellen.

Beispielergebnisse

flag_date flags_count
2023-11-19 2
2023-11-22 1
2023-11-23 1
2023-11-24 2
2023-11-26 1
… …
1 „Gefällt mir“