Informe del panel - Nuevos colaboradores

Datos e informes
, ,
1

Este es un informe de panel SQL para nuevos contribuyentes.

Este informe de panel SQL está diseñado para brindar a los administradores una visión clara de la participación de los usuarios en su foro de Discourse al identificar cuántos usuarios realizaron su primera publicación cada día dentro de un rango de fechas especificado.

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

Explicación de la consulta SQL

El informe obtiene el recuento de usuarios distintos que realizaron su primera publicación cada día dentro del rango de fechas especificado. Lo hace realizando los siguientes pasos:

Parámetros:

  • 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.

Subconsulta: Determinación de la fecha de la primera publicación para cada usuario

Este informe contiene una subconsulta que selecciona dos columnas de la tabla posts: user_id y la marca de tiempo created_at más temprana (denominada first_post_date). La marca de tiempo created_at más temprana representa la primera publicación del usuario. Esta subconsulta incluye una condición WHERE que solo considera las publicaciones que no han sido eliminadas (deleted_at IS NULL). Finalmente, agrupa los resultados por user_id para garantizar que solo estemos considerando la primera publicación de cada usuario.

Consulta principal: Conteo de usuarios que realizaron su primera publicación

La consulta principal realiza las siguientes operaciones:

  • JOIN: La tabla posts principal (denominada p) se une con el resultado de la subconsulta (denominada fp) en user_id para hacer coincidir cada publicación con la primera publicación del usuario correspondiente.
  • Filtrado por fecha: La cláusula WHERE incluye dos condiciones: compara la marca de tiempo created_at de cada publicación con la first_post_date de la subconsulta para garantizar que solo estemos tratando con las primeras publicaciones, y verifica que la marca de tiempo created_at se encuentre dentro del rango de fechas especificado, incluido el end_date (+1 día para incluir el día final por completo).
  • Agregación: Las publicaciones se agrupan luego por fecha, truncadas al día sin componente de hora (date_trunc('day', p.created_at)::date), lo que permite contar los usuarios únicos que publicaron por primera vez cada día.
  • Conteo: Usando COUNT(DISTINCT p.user_id), obtenemos el número de usuarios únicos que realizaron su primera publicación cada día respectivo.
  • Ordenamiento: Los resultados se ordenan por día en orden ascendente (ORDER BY day) para proporcionar una visión general cronológica de la participación del usuario.

Salida final

El informe final consta de dos columnas:

  • day: La fecha de participación del usuario sin el componente de hora.
  • new_contributors: El número de usuarios distintos que realizaron su primera publicación en el foro cada día.

Resultados de ejemplo

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
2 Me gusta