Dashboard-Bericht - Benutzer-zu-Benutzer

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für Benutzer-zu-Benutzer (mit Antworten) und Benutzer-zu-Benutzer (ohne Antworten).

Dieser Bericht zählt die Anzahl der persönlichen Nachrichten, die Benutzer innerhalb eines bestimmten Zeitraums täglich senden, und liefert Einblicke in die Aktivität des Benutzerengagements in der privaten Kommunikation.

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT 
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

Erklärung der SQL-Abfrage

Diese SQL-Abfrage führt die folgenden Aktionen aus:

  • Parameter:
    • :start_date und :end_date, die zum Filtern der Daten innerhalb des angegebenen Datumsbereichs verwendet werden. Beide Datumsparameter akzeptieren das Format JJJJ-MM-TT.
    • :include_replies, um zu bestimmen, ob Antworten auf anfängliche Nachrichten in die Zählung einbezogen werden sollen. Standardmäßig auf false gesetzt, um das Verhalten des Berichts “Benutzer-zu-Benutzer (ohne Antworten)” zu spiegeln. Das Setzen dieses Parameters auf true spiegelt das Verhalten des Berichts “Benutzer-zu-Benutzer (mit Antworten)” wider.
  • Datenauswahl:
    • Die Abfrage wählt das Erstellungsdatum (created_at) aus der Tabelle posts aus, das das Datum darstellt, an dem jede Nachricht erstellt wurde.
  • Join-Operation:
    • Die Abfrage führt einen INNER JOIN zwischen den Tabellen posts und topics über deren id-Felder durch. Dieser Join ist entscheidend, da er jeden Beitrag mit seinem entsprechenden Thema verknüpft und es uns ermöglicht, nur die Beiträge herauszufiltern, die Teil einer privaten Nachrichtenkonversation sind.
  • Filterkriterien: Die Abfrage wendet mehrere Filter an:
    • Sie berücksichtigt nur Beiträge, die Teil eines Themas mit dem archetype ‘private_message’ sind, um sicherzustellen, dass nur persönliche Nachrichten gezählt werden.
    • Sie filtert Beiträge heraus, die keinem Benutzer zugeordnet sind, indem sichergestellt wird, dass user_id größer als 0 ist.
    • Sie grenzt die Ergebnisse weiter auf Themen mit dem subtype ‘user_to_user’ ein, was eine persönliche Nachricht zwischen einzelnen Benutzern anzeigt.
    • Sie schließt gelöschte Beiträge oder Themen aus, indem sie prüft, ob deleted_at für beide Tabellen NULL ist.
    • Sie wendet den Datumsbereichsfilter an, um nur Beiträge einzuschließen, die zwischen :start_date und :end_date erstellt wurden.
    • Wenn :include_replies false ist, wird nur der erste Beitrag in jedem Thema gezählt (p.post_number = 1), Antworten werden ausgeschlossen.
  • Aggregation: Die Abfrage gruppiert die Ergebnisse nach dem Datum, an dem die Beiträge erstellt wurden, was das Zählen der Anzahl der an jedem Datum gesendeten Nachrichten ermöglicht.
  • Sortierung: Schließlich sortiert die Abfrage die Ergebnisse nach dem Datum in aufsteigender Reihenfolge und liefert eine chronologische Abfolge der privaten Nachrichtenaktivität von Benutzer zu Benutzer.

Beispielergebnisse

day personal_messages
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
4 „Gefällt mir“