Informe del Panel - Ratio de Marcado de Usuarios

Datos e informes
, ,
1

Este es una versión SQL del Informe del Panel para la Relación de Señalamiento de Usuarios.

Este informe del panel genera una lista de usuarios ordenados por la relación de respuestas del personal a sus señalamientos (acordados y en desacuerdo). El informe incluye información sobre la actividad de señalamiento de cada usuario, centrándose específicamente en el número de señalamientos que fueron acordados, en desacuerdo o ignorados por los moderadores, así como una puntuación calculada que representa el rendimiento de señalamiento del usuario.

El informe es útil para que los administradores identifiquen a los usuarios que señalan con precisión el contenido inapropiado, midan la participación del usuario en la moderación comunitaria, proporcionen comentarios sobre las prácticas de señalamiento y evalúen el rendimiento de señalamiento del usuario para posibles roles de moderador.

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

Explicación de la Consulta SQL

Aquí hay un desglose de la consulta:

  • Parámetros: La consulta acepta dos parámetros, :start_date y :end_date, que permiten al usuario especificar el rango de fechas para el informe. Ambos parámetros de fecha aceptan el formato de fecha YYYY-MM-DD.
  • Cláusula SELECT: La consulta selecciona las siguientes columnas:
    • user_id: El identificador único del usuario.
    • username: El nombre de usuario del usuario.
    • silenced: Un valor booleano que indica si el usuario está actualmente silenciado.
    • disagreed_flags: El número total de señalamientos de usuario con los que los moderadores no estuvieron de acuerdo.
    • agreed_flags: El número total de señalamientos de usuario con los que los moderadores estuvieron de acuerdo.
    • ignored_flags: El número total de señalamientos de usuario que los moderadores ignoraron.
    • score: Una puntuación calculada que representa el rendimiento de señalamiento del usuario.
  • Cláusula FROM: La consulta une la tabla users con la tabla reviewable_scores en user_id para obtener información de señalamiento relacionada con cada usuario.
  • Cláusula WHERE: La consulta filtra los datos para incluir solo los registros de usuarios con un id mayor que 0 y donde la fecha created_at de las puntuaciones revisables esté dentro del rango de fechas especificado.
  • Cláusula GROUP BY: La consulta agrupa los resultados por user_id, username, uploaded_avatar_id y silenced_till para agregar datos de señalamiento para cada usuario.
  • Cláusula ORDER BY: La consulta ordena los resultados por la score calculada en orden descendente para mostrar primero a los usuarios con el mayor rendimiento de señalamiento.
  • Cláusula LIMIT: La consulta limita los resultados a los 100 usuarios principales según la puntuación calculada.

Ejemplo de Resultados

usuario 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 me gusta