Dashboard-Bericht - Benutzer-Melde-Verhältnis

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für das Verhältnis der Benutzerkennzeichnungen.

Dieser Dashboard-Bericht generiert eine Liste von Benutzern, geordnet nach dem Verhältnis der Antworten des Personals auf ihre Kennzeichnungen (zugestimmt und abgelehnt). Der Bericht enthält Informationen über die Kennzeichnungsaktivität jedes Benutzers, insbesondere über die Anzahl der Kennzeichnungen, denen von Moderatoren zugestimmt, die abgelehnt oder ignoriert wurden, sowie eine berechnete Punktzahl, die die Leistung des Benutzers bei der Kennzeichnung darstellt.

Der Bericht ist nützlich für Administratoren, um Benutzer zu identifizieren, die unangemessene Inhalte korrekt kennzeichnen, das Engagement der Benutzer bei der Community-Moderation einzuschätzen, Feedback zu Kennzeichnungspraktiken zu geben und die Leistung der Benutzer bei der Kennzeichnung für potenzielle Moderatorenrollen zu bewerten.

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

SELECT
  u.id AS user_id,
  u.username,
  CASE
    WHEN u.silenced_till IS NOT NULL THEN 't'
    ELSE 'f'
  END AS silenced,
  SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric AS disagreed_flags,
  SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric AS agreed_flags,
  SUM(CASE WHEN rs.status = 3 THEN 1 ELSE 0 END)::numeric AS ignored_flags,
  (
    CASE
      WHEN SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric = 0 THEN
        SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric * SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric
      ELSE
        ROUND(
          (1 - (SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric / SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END))) *
          (SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) - SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric)
        )
    END
  ) AS score
FROM
  users AS u
  INNER JOIN reviewable_scores AS rs ON rs.user_id = u.id
WHERE
  u.id > 0
  AND rs.created_at >= :start_date
  AND rs.created_at <= :end_date
GROUP BY
  u.id,
  u.username,
  u.uploaded_avatar_id,
  u.silenced_till
ORDER BY
  score DESC
LIMIT 100

SQL-Abfrageerklärung

Hier ist eine Aufschlüsselung der Abfrage:

  • Parameter: Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, mit denen der Benutzer den Datumsbereich für den Bericht festlegen kann. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.
  • SELECT-Klausel: Die Abfrage wählt die folgenden Spalten aus:
    • user_id: Die eindeutige Kennung des Benutzers.
    • username: Der Benutzername des Benutzers.
    • silenced: Ein boolescher Wert, der angibt, ob der Benutzer derzeit stummgeschaltet ist.
    • disagreed_flags: Die Gesamtzahl der Benutzerkennzeichnungen, denen von Moderatoren widersprochen wurde.
    • agreed_flags: Die Gesamtzahl der Benutzerkennzeichnungen, denen von Moderatoren zugestimmt wurde.
    • ignored_flags: Die Gesamtzahl der von Moderatoren ignorierten Benutzerkennzeichnungen.
    • score: Eine berechnete Punktzahl, die die Leistung des Benutzers bei der Kennzeichnung darstellt.
  • FROM-Klausel: Die Abfrage verknüpft die users-Tabelle mit der reviewable_scores-Tabelle über die user_id, um Kennzeichnungsinformationen zu jedem Benutzer zu erhalten.
  • WHERE-Klausel: Die Abfrage filtert die Daten, um nur die Einträge für Benutzer mit einer id größer als 0 und bei denen das created_at-Datum der Überprüfbarkeitsbewertungen innerhalb des angegebenen Datumsbereichs liegt, einzuschließen.
  • GROUP BY-Klausel: Die Abfrage gruppiert die Ergebnisse nach user_id, username, uploaded_avatar_id und silenced_till, um Kennzeichnungsdaten für jeden Benutzer zu aggregieren.
  • ORDER BY-Klausel: Die Abfrage sortiert die Ergebnisse nach der berechneten score in absteigender Reihenfolge, um die Benutzer mit der höchsten Kennzeichnungsleistung zuerst anzuzeigen.
  • LIMIT-Klausel: Die Abfrage beschränkt die Ergebnisse auf die Top 100 Benutzer basierend auf der berechneten Punktzahl.

Beispielergebnisse

user username silenced disagreed_flags agreed_flags ignored_flags score
user_1_id user_1 f 0.0 10.0 3.0 100.0
user_2_id user_2 f 0.0 6.0 3.0 36.0
user_3_id user_3 f 0.0 4.0 0.0 16.0
… … … … … … …
1 „Gefällt mir“