Informe del panel - de usuario a usuario

Datos e informes
, ,
1

Este es una versión SQL del Informe del Panel para Usuario a Usuario (con respuestas) y Usuario a Usuario (excluyendo respuestas).

Este informe cuenta el número de mensajes personales enviados por los usuarios cada día, dentro de un rango de fechas especificado, proporcionando información sobre la actividad de participación del usuario en la comunicación privada.

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

Explicación de la consulta SQL

Esta consulta SQL realiza las siguientes acciones:

  • Parámetros:
    • :start_date y :end_date, que se utilizan para filtrar los datos dentro del rango de fechas especificado. Ambos parámetros de fecha aceptan el formato YYYY-MM-DD.
    • :include_replies para determinar si las respuestas a los mensajes iniciales deben incluirse en el recuento. Se establece en false por defecto para reflejar el comportamiento del informe Usuario a Usuario (excluyendo respuestas). Establecer este parámetro en true reflejará el comportamiento del informe Usuario a Usuario (con respuestas).
  • Selección de datos:
    • La consulta selecciona la fecha created_at de la tabla posts, que representa la fecha en que se creó cada mensaje.
  • Operación de unión:
    • La consulta realiza una INNER JOIN entre las tablas posts y topics en sus campos id. Esta unión es crucial ya que vincula cada publicación a su tema correspondiente, lo que nos permite filtrar solo aquellas publicaciones que forman parte de una conversación de mensaje personal.
  • Criterios de filtrado: La consulta aplica varios filtros:
    • Solo considera las publicaciones que forman parte de un tema con un archetype de ‘private_message’, asegurando que solo se cuenten los mensajes personales.
    • Filtra cualquier publicación que no esté asociada con un usuario asegurando que user_id sea mayor que 0.
    • Reduce aún más los resultados a temas con un subtype de ‘user_to_user’, lo que indica un mensaje personal entre usuarios individuales.
    • Excluye cualquier publicación o tema que haya sido eliminado verificando que deleted_at sea NULL para ambas tablas.
    • Aplica el filtro de rango de fechas para incluir solo las publicaciones creadas entre :start_date y :end_date.
    • Si :include_replies es falso, solo se cuenta la primera publicación de cada tema (p.post_number = 1), excluyendo las respuestas.
  • Agregación: La consulta agrupa los resultados por la fecha en que se crearon las publicaciones, lo que permite contar el número de mensajes enviados en cada fecha.
  • Clasificación: Finalmente, la consulta ordena los resultados por fecha en orden ascendente, proporcionando una secuencia cronológica de la actividad de mensajes personales de usuario a usuario.

Resultados de ejemplo

día mensajes_personales
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
4 Me gusta