Informe del panel - Marcadores

[SaraDev (Sara Devlaeminck)]

Este es una versión SQL del Informe del Panel para Marcadores.

Este informe del panel proporciona un recuento diario de marcadores creados dentro de un rango de fechas especificado.

-- [params]
-- date :start_date
-- date :end_date

WITH bookmark_counts AS (
  SELECT
    DATE(bookmarks.created_at) AS bookmark_date,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Topic' THEN bookmarks.id END) AS topic_bookmarks,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Post' THEN bookmarks.id END) AS post_bookmarks
  FROM bookmarks
  WHERE bookmarks.created_at::date BETWEEN :start_date AND :end_date
  GROUP BY bookmark_date
)
SELECT
  bookmark_date,
  topic_bookmarks + post_bookmarks AS total_bookmarks,
  topic_bookmarks,
  post_bookmarks
FROM bookmark_counts
ORDER BY bookmark_date

Explicación de la Consulta SQL

Parámetros

Para generar el informe, necesitas proporcionar dos parámetros: :start_date y :end_date. Estos parámetros definen el rango de fechas para el cual deseas analizar la actividad de marcadores. Ambos parámetros de fecha aceptan el formato de fecha YYYY-MM-DD.

Bookmark_Counts (CTE)

Se utiliza una Expresión Común de Tabla (CTE) llamada bookmark_counts para organizar los datos antes de la selección final. La CTE realiza las siguientes acciones:

• Filtrado de Fechas: Filtra los marcadores por su fecha de creación para incluir solo aquellos creados dentro de las fechas de inicio y fin proporcionadas (:start_date y :end_date).
• Agrupación por Fecha: Agrupa los resultados por la fecha de creación de los marcadores, asegurando que los recuentos se agreguen diariamente.
• Conteo de Marcadores: Cuenta el número de marcadores para temas y publicaciones por separado, utilizando DISTINCT para asegurar que cada marcador se cuente solo una vez. Esto se hace utilizando agregación condicional con sentencias CASE:
  • topic_bookmarks cuenta los marcadores donde bookmarkable_type es ‘Topic’.
  • post_bookmarks cuenta los marcadores donde bookmarkable_type es ‘Post’.

Consulta Principal

Después de que la CTE ha organizado los datos, la sentencia SELECT final recupera las siguientes columnas:

1. bookmark_date: La fecha para la cual se agregan los recuentos.
2. total_bookmarks: La suma de los marcadores de temas y publicaciones para cada día.
3. topic_bookmarks: El recuento de marcadores para temas para cada día. Ej:
   
   
4. post_bookmarks: El recuento de marcadores para publicaciones para cada día. Ej:
   
   

Luego, los resultados se ordenan por bookmark_date para presentar los datos en orden cronológico.

Resultados de Ejemplo

bookmark_date	total_bookmarks	topic_bookmarks	post_bookmarks
2023-12-01	16	1	15
2023-12-02	4	1	3
2023-12-03	8	0	8
2023-12-04	19	9	10
2023-12-05	18	3	15
…	…	…	…