Informe del panel - Usuarios más ignorados / silenciados

Datos e informes
, ,
1

Este es una versión SQL del informe del panel para usuarios ignorados/silenciados principales.

Este informe del panel proporciona a los administradores información sobre qué usuarios de la plataforma han sido ignorados o silenciados por otros miembros entre fechas de inicio y fin especificadas. Un usuario ignorado es aquel que ha sido filtrado selectivamente por un miembro para que sus publicaciones no sean visibles para él, mientras que un usuario silenciado es aquel del que un miembro ha optado por no recibir notificaciones.

Este informe puede identificar a los usuarios que están causando fricción dentro de una comunidad, o cuyo comportamiento está llevando a otros a no querer interactuar con ellos. El informe puede ayudar en la toma de decisiones de moderación, como contactar a los usuarios frecuentemente ignorados o silenciados para abordar su comportamiento, o comprender de manera más amplia si hay problemas en la comunidad que requieran atención.

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

Explicación de la consulta SQL

Esta consulta SQL funciona en varios pasos:

  • Parámetros de fecha:
    • La consulta acepta dos parámetros, :start_date y :end_date, que definen el rango de fechas para el informe. Ambos parámetros de fecha aceptan el formato de fecha YYYY-MM-DD.
  • Expresiones Comunes de Tabla (CTE):
    • La CTE ignored_users selecciona el ignored_user_id y cuenta cuántas veces aparece ese ID en la tabla ignored_users (lo que indica cuántas veces se ha ignorado al usuario), filtrando los resultados entre :start_date y :end_date.
    • La CTE muted_users es similar pero selecciona el muted_user_id de la tabla muted_users y cuenta las instancias dentro de los filtros de fecha.
  • Declaración SELECT principal: Selecciona los detalles del usuario de la tabla users y los recuentos de las CTE ignored_users y muted_users.
    • ignores_count se toma directamente de la CTE ignored_users.
    • mutes_count se toma de la CTE muted_users, pero si es NULL (lo que significa que el usuario no ha sido silenciado), se reemplaza con 0 usando la función COALESCE.
    • Se calcula un total sumando los ignorados y los silenciados.
  • JOINS: La consulta principal utiliza un JOIN en ignored_users para incluir a todos los usuarios que han sido ignorados al menos una vez, y un LEFT OUTER JOIN en muted_users para incluir también a los usuarios que quizás no hayan sido silenciados.
  • ORDER BY: El informe se ordena por el total de ignorados y silenciados en orden descendente, mostrando a los usuarios más ‘problemáticos’ en la parte superior del informe.

Ejemplo de resultados

usuario nombre de usuario ignores_count mutes_count total
usuario1 usuario1 4 1 5
usuario2 usuario2 3 0 3
usuario3 usuario3 1 2 3
3 Me gusta