Dashboard-Bericht - Wachstum der Vertrauensstufe

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für das Wachstum des Vertrauensniveaus.

Dieser Dashboard-Bericht zählt die Anzahl der Benutzer, die innerhalb des angegebenen Datumsbereichs täglich die Vertrauensstufen 1, 2, 3 und 4 erreichen, und hilft Administratoren, das Benutzerengagement und den Fortschritt durch die Vertrauensstufen in einer Discourse-Community zu verfolgen.

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

SQL-Abfrageerklärung

Die Abfrage wählt Datensätze aus der Tabelle user_histories aus, die Änderungen im Benutzerstatus, einschließlich ihrer Vertrauensstufen, protokolliert.

Hier ist eine Aufschlüsselung der Abfrage:

  • Datums-Parameter: Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, die den Datumsbereich für den Bericht definieren. Beide Datums-Parameter akzeptieren das Datumsformat JJJJ-MM-TT.
  • date(created_at) AS date: Dieser Teil der SELECT-Anweisung extrahiert das Datum aus dem Zeitstempel created_at und bezeichnet es für jeden Datensatz als ‘date’. Dies wird verwendet, um die Ergebnisse nach Tag zu gruppieren.
  • COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: Dieser Abschnitt zählt die Anzahl der Benutzer, die zu einer bestimmten Vertrauensstufe (Y) von einer niedrigeren Vertrauensstufe (weniger als X) aufgestiegen sind. Die FILTER-Klausel wird verwendet, um nur die Zeilen einzuschließen, die der angegebenen Bedingung entsprechen. Der ::integer-Cast wird verwendet, um den previous_value für den Vergleich in einen Integer umzuwandeln. Davon gibt es vier Zählspalten, eine für jede Vertrauensstufe von 1 bis 4.
  • FROM user_histories: Dies gibt die Tabelle an, aus der die Daten abgerufen werden sollen.
  • WHERE created_at >= :start_date AND created_at <= :end_date: Dies filtert die Datensätze, um nur diejenigen innerhalb des angegebenen Datumsbereichs einzuschließen.
  • AND (action = 2 OR action = 15): Dies filtert die Datensätze, um nur diejenigen einzuschließen, bei denen die Aktion eine Änderung der Vertrauensstufe anzeigt. Die spezifischen Aktionen, die Änderungen der Vertrauensstufe entsprechen, werden durch die Zahlen 2 und 15 dargestellt.
  • GROUP BY date(created_at): Dies gruppiert die Ergebnisse nach dem Datum, sodass Sie eine Zählung der Änderungen der Vertrauensstufe für jeden Tag innerhalb des angegebenen Bereichs erhalten.
  • ORDER BY date(created_at): Dies ordnet die Ergebnisse chronologisch nach Datum.

Beispielergebnisse

date tl1_reached tl2_reached tl3_reached tl4_reached
2024-01-01 1 3 1 0
2024-01-02 5 1 0 1
2024-01-03 8 2 3 0
… … … … …
4 „Gefällt mir“