Informe del panel - Mejores usuarios por Likes recibidos de un usuario con un nivel de confianza más bajo

Datos e informes
, ,
1

Este es una versión SQL del informe del panel de control de los usuarios principales por “me gusta” recibidos de un usuario con un nivel de confianza inferior.

Este informe del panel de control tiene como objetivo identificar qué usuarios han recibido la mayor cantidad de “me gusta” de miembros de niveles de confianza inferiores dentro de un período de tiempo especificado. El informe se centra en la interacción entre usuarios de diferentes niveles de confianza y destaca las cuentas que destacan en la participación de la comunidad al recibir “me gusta” de aquellos con permisos o experiencia inferiores en el foro.

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

WITH user_liked_tl_lower AS (
    SELECT
        users.id AS user_id,
        users.username,
        users.trust_level,
        COUNT(*) AS likes,
        rank() OVER (
            PARTITION BY users.trust_level
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM users
    INNER JOIN posts p ON p.user_id = users.id
    INNER JOIN user_actions ua ON ua.target_post_id = p.id AND ua.action_type = 1
    INNER JOIN users u_liked ON ua.user_id = u_liked.id AND u_liked.trust_level < users.trust_level
    WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY users.id
    ORDER BY trust_level DESC, likes DESC
)

SELECT * FROM user_liked_tl_lower
WHERE rank <= 10

Explicación de la consulta SQL

  • 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.
  • Expresión Común de Tabla (CTE):
    • La CTE user_liked_tl_lower se define al principio para su reutilización dentro de la consulta principal.
    • Calcula el número total de “me gusta” que cada usuario ha recibido en sus publicaciones de usuarios con niveles de confianza inferiores dentro del período de tiempo dado definido por :start_date y :end_date.
  • Uniones Internas (Inner Joins):
    • Se realizan uniones entre la tabla users y las tablas posts y user_actions para filtrar las acciones que son ‘me gusta’ (indicado por action_type = 1).
    • Se realiza una unión adicional a la tabla users (u_liked) para garantizar que a quien le dio “me gusta” tenga un nivel de confianza inferior al del autor de la publicación.
  • Clasificación (Ranking):
    • Los resultados se particionan por el nivel de confianza del usuario y se ordenan por el recuento de “me gusta” en orden descendente.
    • Se asigna una clasificación basada en el número de “me gusta”, donde los empates reciben la misma clasificación y la siguiente clasificación salta números en consecuencia (esta es una clasificación estándar, no una clasificación densa).
  • Filtrado de resultados en CTE:
    • Solo se cuentan los “me gusta” que se dieron dentro del período de tiempo especificado.
  • Selección Final:
    • La consulta principal selecciona todas las columnas de la CTE donde el rank es 10 o inferior.
    • Esto limita los resultados a los 10 usuarios principales en cada categoría de nivel de confianza que han recibido la mayor cantidad de “me gusta” de usuarios de niveles de confianza inferiores.

Resultados de ejemplo

usuario nombre de usuario nivel de confianza me gusta clasificación
user1 user1 4 323 1
user2 user2 4 164 2
user3 user3 4 143 3
… … … …
user11 user11 3 45 1
user12 user12 3 34 2
… … … …
3 Me gusta