Informe del panel - Usuarios principales por Me gusta recibidos de una variedad de personas

Datos e informes
, ,
1

Esta es una versión SQL del Informe del Panel para los Usuarios Principales por Me gusta Recibidos de una Variedad de Personas.

Este informe del panel muestra los usuarios principales que han recibido me gusta de una amplia gama de usuarios de un sitio, durante un rango de fechas especificado. La consulta está diseñada para averiguar qué usuarios recibieron la mayor cantidad de me gusta en sus publicaciones, y cada recuento de me gusta representa el número de usuarios únicos que dieron me gusta a sus publicaciones.

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

SELECT
    p.user_id,
    u.username,
    COUNT(DISTINCT ua.user_id) AS likes
FROM
    user_actions ua
    INNER JOIN posts p ON p.id = ua.target_post_id
    INNER JOIN users u ON p.user_id = u.id
WHERE
    ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type = 1
    AND p.user_id > 0
GROUP BY
    p.user_id,
    u.username
ORDER BY
    likes DESC

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.
  • Selección: Las columnas principales seleccionadas para la salida son:
    • p.user_id: El ID de usuario del publicador.
    • u.username: El nombre del usuario correspondiente.
    • likes: Un recuento de los ID de usuario distintos (ua.user_id) que han dado ‘me gusta’ a las publicaciones, asegurando que se calculen los me gusta únicos por usuario.
  • Uniones: Hay dos uniones internas:
    • user_actions ua con posts p: Coincide las acciones con sus publicaciones correspondientes.
    • posts p con users u: Conecta las publicaciones con sus autores, facilitando la recuperación de la información del usuario.
  • Filtrado:
    • La cláusula WHERE aplica varios filtros:
      • ua.created_at::date BETWEEN :start_date AND :end_date limita las acciones consideradas a aquellas dentro de los parámetros.
      • ua.action_type = 1 aísla las acciones de ‘me gusta’ de otros tipos de acciones de usuario.
      • p.user_id > 0 excluye cualquier me gusta asociado con usuarios no registrados (como usuarios del sistema o anónimos).
  • Agrupación:
    • El conjunto de resultados se agrupa por p.user_id y u.username. Esto asegura que los me gusta se agreguen por usuario y nombre de usuario correspondiente.
  • Ordenación:
    • Los resultados se ordenan por el recuento de me gusta (likes DESC), presentando primero a los usuarios más apreciados.

Ejemplo de Resultados

usuario nombre de usuario me gusta
user1 user1 100
user2 user2 90
user3 user3 80
… … …
3 Me gusta