Dashboard-Bericht - Neue Mitwirkende

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für neue Mitwirkende.

Dieser SQL-Dashboard-Bericht soll Administratoren einen klaren Überblick über das Benutzerengagement auf ihrem Discourse-Forum geben, indem ermittelt wird, wie viele Benutzer an jedem Tag innerhalb eines bestimmten Zeitraums ihren ersten Beitrag geleistet haben.

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

Erklärung der SQL-Abfrage

Der Bericht ruft die Anzahl der eindeutigen Benutzer ab, die ihren ersten Beitrag an jedem Tag innerhalb des angegebenen Zeitraums geleistet haben. Dies geschieht durch die folgenden Schritte:

Parameter:

  • Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, die den Zeitraum für den Bericht definieren. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.

Innere Abfrage: Ermittlung des Datums des ersten Beitrags für jeden Benutzer

Dieser Bericht enthält eine Unterabfrage, die zwei Spalten aus der Tabelle posts auswählt: user_id und den frühesten created_at-Zeitstempel (alias first_post_date). Der früheste created_at-Zeitstempel repräsentiert den ersten Beitrag des Benutzers. Diese Unterabfrage enthält eine WHERE-Bedingung, die nur Beiträge berücksichtigt, die nicht gelöscht wurden (deleted_at IS NULL). Schließlich gruppiert sie die Ergebnisse nach user_id, um sicherzustellen, dass wir nur den ersten Beitrag jedes Benutzers betrachten.

Hauptabfrage: Zählen von Benutzern, die ihren ersten Beitrag geleistet haben

Die Hauptabfrage führt die folgenden Operationen durch:

  • JOIN: Die Haupttabelle posts (alias p) wird mit dem Ergebnis der Unterabfrage (alias fp) über user_id verbunden, um jeden Beitrag mit dem ersten Beitrag des entsprechenden Benutzers abzugleichen.
  • Filterung nach Datum: Die WHERE-Klausel enthält zwei Bedingungen - sie vergleicht den created_at-Zeitstempel jedes Beitrags mit dem first_post_date aus der Unterabfrage, um sicherzustellen, dass wir nur mit den ersten Beiträgen arbeiten, und sie prüft, ob der created_at-Zeitstempel innerhalb des angegebenen Zeitraums liegt, einschließlich des end_date (+1 Tag, um den Endtag vollständig einzuschließen).
  • Aggregation: Die Beiträge werden dann nach dem Datum gruppiert, auf den Tag ohne Zeitkomponente gekürzt (date_trunc('day', p.created_at)::date), was die Zählung der eindeutigen Benutzer ermöglicht, die an jedem Tag zum ersten Mal gepostet haben.
  • Zählung: Mit COUNT(DISTINCT p.user_id) erhalten wir die Anzahl der eindeutigen Benutzer, die an jedem jeweiligen Tag ihren ersten Beitrag geleistet haben.
  • Sortierung: Die Ergebnisse werden nach dem Tag aufsteigend sortiert (ORDER BY day), um einen chronologischen Überblick über das Benutzerengagement zu geben.

Endergebnis

Der endgültige Bericht besteht aus zwei Spalten:

  • day: Das Datum des Benutzerengagements ohne Zeitkomponente.
  • new_contributors: Die Anzahl der eindeutigen Benutzer, die an jedem Tag ihren ersten Beitrag im Forum geleistet haben.

Beispielergebnisse

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
2 „Gefällt mir“