Dashboard-Bericht - Top ignorierte/stummgeschaltete Benutzer

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für die am häufigsten ignorierten/stummen Benutzer.

Dieser Dashboard-Bericht gibt Administratoren Einblicke, welche Benutzer auf der Plattform zwischen angegebenen Start- und Enddaten von anderen Mitgliedern ignoriert oder stummgeschaltet wurden. Ein ignorierter Benutzer ist jemand, der von einem Mitglied selektiv ausgeblendet wurde, sodass seine Beiträge für ihn nicht sichtbar sind, während ein stummgeschalteter Benutzer jemand ist, von dem ein Mitglied keine Benachrichtigungen erhalten möchte.

Dieser Bericht kann Benutzer identifizieren, die für Reibungen innerhalb einer Community sorgen oder deren Verhalten dazu führt, dass andere nicht mit ihnen interagieren möchten. Der Bericht kann bei Moderationsentscheidungen helfen, wie z. B. die Kontaktaufnahme mit häufig ignorierten oder stummgeschalteten Benutzern, um deren Verhalten anzusprechen, oder um breiter zu verstehen, ob es Probleme in der Community gibt, die Aufmerksamkeit erfordern.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-01

WITH ignored_users AS (
    SELECT
        ignored_user_id AS user_id,
        COUNT(*) AS ignores_count
    FROM ignored_users
    WHERE created_at >= :start_date
      AND created_at <= :end_date
    GROUP BY ignored_user_id
    ORDER BY COUNT(*) DESC
),
muted_users AS (
    SELECT
        muted_user_id AS user_id,
        COUNT(*) AS mutes_count
    FROM muted_users
    WHERE created_at >= :start_date
      AND created_at <= :end_date
    GROUP BY muted_user_id
    ORDER BY COUNT(*) DESC
)

SELECT
    u.id AS user_id,
    u.username AS username,
    ig.ignores_count AS ignores_count,
    COALESCE(mu.mutes_count, 0) AS mutes_count,
    ig.ignores_count + COALESCE(mu.mutes_count, 0) AS total
FROM users AS u
JOIN ignored_users AS ig ON ig.user_id = u.id
LEFT OUTER JOIN muted_users AS mu ON mu.user_id = u.id
ORDER BY total DESC

Erklärung der SQL-Abfrage

Diese SQL-Abfrage funktioniert in mehreren Schritten:

  • Datums-Parameter:
    • Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, die den Datumsbereich für den Bericht definieren. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.
  • Common Table Expressions (CTEs):
    • Die CTE ignored_users wählt die ignored_user_id aus und zählt, wie oft diese ID in der Tabelle ignored_users vorkommt (was angibt, wie oft der Benutzer ignoriert wurde), wobei die Ergebnisse zwischen :start_date und :end_date gefiltert werden.
    • Die CTE muted_users ist ähnlich, wählt jedoch die muted_user_id aus der Tabelle muted_users aus und zählt die Vorkommen innerhalb der Datumsfilter.
  • Haupt-SELECT-Anweisung: Wählt die Benutzerdetails aus der Tabelle users und die Zählungen aus den CTEs ignored_users und muted_users aus.
    • ignores_count wird direkt aus der CTE ignored_users übernommen.
    • mutes_count wird aus der CTE muted_users übernommen, aber wenn es NULL ist (was bedeutet, dass der Benutzer nicht stummgeschaltet wurde), wird es mit der Funktion COALESCE durch 0 ersetzt.
    • Eine Gesamtsumme wird durch Addition von Ignorierungen und Stummschaltungen berechnet.
  • JOINS: Die Hauptabfrage verwendet einen JOIN mit ignored_users, um alle Benutzer einzuschließen, die mindestens einmal ignoriert wurden, und einen LEFT OUTER JOIN mit muted_users, um auch Benutzer einzuschließen, die möglicherweise nicht stummgeschaltet wurden.
  • ORDER BY: Der Bericht wird nach der Gesamtzahl der Ignorierungen und Stummschaltungen in absteigender Reihenfolge sortiert, sodass die „problematischsten“ Benutzer am Anfang des Berichts angezeigt werden.

Beispielergebnisse

Benutzer Benutzername ignoriert_Anzahl stummgeschaltet_Anzahl Gesamt
user1 user1 4 1 5
user2 user2 3 0 3
user3 user3 1 2 3
3 „Gefällt mir“