Rapport de tableau de bord - Ratio de signalement des utilisateurs

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour le ratio de signalement des utilisateurs.

Ce rapport de tableau de bord génère une liste d’utilisateurs classés par le ratio des réponses du personnel à leurs signalements (approuvés et désapprouvés). Le rapport comprend des informations sur l’activité de signalement de chaque utilisateur, en se concentrant spécifiquement sur le nombre de signalements qui ont été approuvés, désapprouvés ou ignorés par les modérateurs, ainsi qu’un score calculé représentant la performance de signalement de l’utilisateur.

Le rapport est utile aux administrateurs pour identifier les utilisateurs qui signalent avec précision le contenu inapproprié, évaluer l’engagement des utilisateurs dans la modération communautaire, fournir des commentaires sur les pratiques de signalement et évaluer la performance de signalement des utilisateurs pour d’éventuels rôles de modérateur.

-- [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

Explication de la requête SQL

Voici une ventilation de la requête :

  • Paramètres : La requête accepte deux paramètres, :start_date et :end_date, qui permettent à l’utilisateur de spécifier la plage de dates pour le rapport. Les deux paramètres de date acceptent le format de date AAAA-MM-JJ.
  • Clause SELECT : La requête sélectionne les colonnes suivantes :
    • user_id : L’identifiant unique de l’utilisateur.
    • username : Le nom d’utilisateur de l’utilisateur.
    • silenced : Une valeur booléenne indiquant si l’utilisateur est actuellement réduit au silence.
    • disagreed_flags : Le nombre total de signalements d’utilisateurs qui ont été désapprouvés par les modérateurs.
    • agreed_flags : Le nombre total de signalements d’utilisateurs qui ont été approuvés par les modérateurs.
    • ignored_flags : Le nombre total de signalements d’utilisateurs qui ont été ignorés par les modérateurs.
    • score : Un score calculé représentant la performance de signalement de l’utilisateur.
  • Clause FROM : La requête joint la table users avec la table reviewable_scores sur user_id pour obtenir des informations de signalement relatives à chaque utilisateur.
  • Clause WHERE : La requête filtre les données pour inclure uniquement les enregistrements des utilisateurs dont l’id est supérieur à 0 et dont la date created_at des scores examinables se situe dans la plage de dates spécifiée.
  • Clause GROUP BY : La requête regroupe les résultats par user_id, username, uploaded_avatar_id et silenced_till pour agréger les données de signalement pour chaque utilisateur.
  • Clause ORDER BY : La requête trie les résultats par le score calculé dans l’ordre décroissant pour afficher d’abord les utilisateurs ayant les meilleures performances de signalement.
  • Clause LIMIT : La requête limite les résultats aux 100 meilleurs utilisateurs en fonction du score calculé.

Exemple de résultats

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 « J'aime »