Informe del panel - Usuarios principales por Me gusta recibidos

Datos e informes
, ,
1

Este es una versión SQL del informe del panel de control de los principales usuarios por “me gusta” recibidos.

Este informe del panel de control muestra los principales usuarios de un sitio por la cantidad de “me gusta” recibidos en todas las publicaciones. El informe ayuda a los administradores a identificar a los usuarios altamente comprometidos contando los “me gusta” recibidos dentro de un período determinado, lo que ayuda a reconocer a los miembros activos, evaluar la calidad del contenido, monitorear las tendencias de participación de los usuarios y tomar decisiones informadas de gestión de la comunidad, como la selección de moderadores.

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

SELECT
ua.user_id AS user_id,
u.username as username,
COUNT(*) likes_received
FROM user_actions ua
INNER JOIN users u on ua.user_id = u.id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
AND ua.action_type = 2
GROUP BY ua.user_id, u.username, u.uploaded_avatar_id
ORDER BY likes_received DESC

Explicación de la consulta SQL

La consulta funciona de la siguiente manera:

  • 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.
  • Selección de datos: La consulta selecciona los ID de usuario y los nombres de usuario de la tabla user_actions (ua) y la tabla users (u), que se unen mediante el comando INNER JOIN de SQL bajo la condición de que ua.user_id coincida con u.id.
  • Filtrado de datos: La cláusula WHERE se utiliza para filtrar las acciones que se encuentran dentro del rango de fechas especificado y solo considera las acciones del tipo was_liked, que se representa con action_type = 2.
  • Agregación: La función COUNT(*) en combinación con la cláusula GROUP BY agrupa los resultados por user_id, username y uploaded_avatar_id, y cuenta la cantidad de “me gusta” que ha recibido cada usuario.
  • Ordenación de resultados: La cláusula ORDER BY ordena los datos agregados de manera descendente según likes_received, de modo que el usuario con la mayor cantidad de “me gusta” recibidos esté en la parte superior del informe.

La consulta produce una lista de usuarios junto con la cantidad de “me gusta” que han recibido en el rango de fechas dado, ordenados de mayor a menor.

Resultados de ejemplo

usuario nombre de usuario me gusta recibidos
usuario1 usuario1 748
usuario2 usuario2 324
usuario3 usuario3 308
… … …
2 Me gusta