Informe del panel - Crecimiento del nivel de confianza

Datos e informes
, ,
1

Este es un informe de panel de control en versión SQL para el crecimiento del nivel de confianza.

Este informe de panel cuenta el número de usuarios que alcanzan los niveles de confianza 1, 2, 3 y 4 cada día dentro del rango de fechas especificado, y ayuda a los administradores a rastrear la participación y progresión de los usuarios a través de los niveles de confianza en una comunidad de Discourse.

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

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

Explicación de la consulta SQL

La consulta funciona seleccionando registros de la tabla user_histories, que registra los cambios en los estados de los usuarios, incluidos sus niveles de confianza.

Aquí hay un desglose de la consulta:

  • 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.
  • date(created_at) AS date: Esta parte de la declaración SELECT extrae la fecha de la marca de tiempo created_at y la etiqueta como ‘date’ para cada registro. Esto se usará para agrupar los resultados por día.
  • COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: Esta sección cuenta el número de usuarios que han progresado a un nivel de confianza específico (Y) desde un nivel de confianza inferior (menos de X). La cláusula FILTER se utiliza para incluir solo las filas que coinciden con la condición especificada. La conversión ::integer se utiliza para convertir previous_value a un entero para la comparación. Hay cuatro de estas columnas de recuento, una para cada nivel de confianza de 1 a 4.
  • FROM user_histories: Esto especifica la tabla de la que se recuperarán los datos.
  • WHERE created_at >= :start_date AND created_at <= :end_date: Esto filtra los registros para incluir solo aquellos dentro del rango de fechas especificado.
  • AND (action = 2 OR action = 15): Esto filtra los registros para incluir solo aquellos donde la acción indica un cambio en el nivel de confianza. Las acciones específicas que corresponden a los cambios de nivel de confianza están representadas por los números 2 y 15.
  • GROUP BY date(created_at): Esto agrupa los resultados por fecha, por lo que se obtiene un recuento de los cambios de nivel de confianza para cada día dentro del rango especificado.
  • ORDER BY date(created_at): Esto ordena los resultados cronológicamente por fecha.

Resultados de ejemplo

date tl1_reached tl2_reached tl3_reached tl4_reached
2024-01-01 1 3 1 0
2024-01-02 5 1 0 1
2024-01-03 8 2 3 0
… … … … …
4 Me gusta