Benutzerbeiträge nach E-Mail-Domain in den letzten 7 Tagen

Daten & Berichterstattung
1

Abfrage zum Zählen von Beiträgen nach E-Mail-Domäne für die letzten 7 Tage, ausgenommen automatische Gruppen (Bots, System)

SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extrahiert den Domänenteil der E-Mail
  COUNT(p.id) AS post_count
FROM
  posts p
  JOIN users u ON p.user_id = u.id
  JOIN user_emails ue ON u.id = ue.user_id
    AND ue.primary = TRUE -- Stellt sicher, dass die primäre E-Mail verwendet wird
  LEFT JOIN group_users gu ON gu.user_id = u.id
  LEFT JOIN GROUPS g ON gu.group_id = g.id
    AND g.automatic = TRUE
WHERE
  p.created_at >= CURRENT_DATE - INTERVAL '7 days' -- Filtert Beiträge der letzten 7 Tage
  AND g.id IS NULL -- Stellt sicher, dass Benutzer nicht Teil von automatischen Gruppen sind
GROUP BY
  email_domain
ORDER BY
  post_count DESC -- Sortiert die Ergebnisse nach der Anzahl der Beiträge in absteigender Reihenfolge
2 „Gefällt mir“
2

Das scheint nicht richtig. Was ist die Absicht?

Beiträge der letzten Woche zählen, gruppiert nach E-Mail-Domäne, aber Systembenutzer ausschließen?

Müssen gelöschte Beiträge oder Beiträge aus gelöschten Themen gezählt werden, kleine Aktionsbeiträge oder solche in PMs ausgeschlossen werden?

3

Dies war ein schneller und schmutziger Test des SQL Helper Bots, um die Anzahl der Beiträge nach Domain zu ermitteln. Der Anwendungsfall könnte für Verbände oder Mitgliederforen sein, die verfolgen möchten, welche Untergruppen im Forum am aktivsten sind.

Es könnte sicherlich Verfeinerungen gebrauchen, wie z. B. das Entfernen von gelöschten oder kleinen Aktionsbeiträgen, PMs usw., aber ich dachte, ich würde es posten, falls jemand davon inspiriert werden könnte.

4

Ich glaube, dass dies aufgrund der Gruppenbeitritte tatsächlich überhöhte Zahlen liefert (außerdem gehört jeder zu einer automatischen Gruppe für sein Vertrauensniveau, daher gibt es hier eine gewisse Inkonsistenz). Ich denke, wir können diese entfernen und vielleicht ein paar Anpassungen vornehmen, um Themen, Beiträge und PMs zu fokussieren.

Ich versuche es mal.

Vielleicht so etwas wie das hier:

-- [params]
-- string :interval = 7 days


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extrahiert den Domain-Teil der E-Mail
  COUNT(p.id) AS "Gesamtbeiträge",
  COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS "Themen",
  COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS "Beiträge",
  COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS "Persönliche Nachrichtenbeiträge"
FROM posts p
  JOIN user_emails ue ON p.user_id = ue.user_id AND ue.primary = TRUE -- Stellt sicher, dass die primäre E-Mail verwendet wird
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- Filtert Beiträge aus den letzten x Zeit basierend auf dem Parameter
  AND p.user_id > 0 -- Schließt Systembenutzer (System und Bots) aus
  AND p.deleted_at ISNULL
  AND t.deleted_at ISNULL
  AND p.post_type <> 3
GROUP BY email_domain
ORDER BY "Gesamtbeiträge" DESC -- Sortiert die Ergebnisse nach der Anzahl der Beiträge absteigend
2 „Gefällt mir“
5

2 Beiträge wurden in ein neues Thema aufgeteilt: User Stats plus email

6

Dieses Thema wurde 30 Tage nach der letzten Antwort automatisch geschlossen. Neue Antworten sind nicht mehr möglich.