Lectores y Usuarios Activos Por Día

Datos e informes
1

Este informe SQL proporciona un recuento diario de lectores (usuarios que solo leen publicaciones) y usuarios activos (usuarios que han creado al menos una publicación durante el día), durante un rango de fechas especificado, con un filtro opcional para el ID de categoría.

Este informe está diseñado para ayudar a los administradores a comprender cuán activa está su comunidad, no solo en términos de creación de contenido sino también en consumo de contenido. Esta doble perspectiva permite una comprensión más detallada de la participación del usuario, destacando los días con alta actividad de lectura o publicación.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- int :reader_post_read_count = 10
-- null int :category_id

WITH posts_by_user AS (
    SELECT
        p.user_id,
        p.created_at::date AS post_date,
        p.topic_id,
        COUNT(*) AS posts
    FROM posts p
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, post_date, p.topic_id
), 

posts_read_by_user AS (
    SELECT
        uv.user_id,
        uv.visited_at::date AS visit_date,
        SUM(uv.posts_read) AS posts_read
    FROM user_visits uv
    LEFT JOIN group_users gu ON gu.user_id = uv.user_id
    LEFT JOIN groups g ON g.id = gu.group_id
    LEFT JOIN posts p ON p.user_id = uv.user_id AND p.created_at BETWEEN :start_date AND :end_date
    LEFT JOIN topics t ON t.id = p.topic_id AND t.category_id = :category_id
    WHERE uv.visited_at BETWEEN :start_date AND :end_date
    AND (:category_id IS NULL OR t.category_id IS NOT NULL)
    GROUP BY uv.user_id, visit_date
),

active_users_per_day AS (
    SELECT
        pbu.post_date,
        COUNT(DISTINCT pbu.user_id) AS active_user_count
    FROM posts_by_user pbu
    JOIN topics t ON pbu.topic_id = t.id
    WHERE posts > 0
    AND (:category_id IS NULL OR t.category_id = :category_id)
    GROUP BY post_date
),

readers_per_day AS (
    SELECT
        prbu.visit_date,
        COUNT(DISTINCT prbu.user_id) AS reader_count
    FROM posts_read_by_user prbu
    LEFT JOIN posts_by_user pbu ON prbu.user_id = pbu.user_id AND prbu.visit_date = pbu.post_date
    WHERE pbu.posts IS NULL
    AND prbu.posts_read > :reader_post_read_count
    GROUP BY prbu.visit_date
)

SELECT
    rpd.visit_date,
    rpd.reader_count,
    COALESCE(aupd.active_user_count, 0) AS active_user_count
FROM readers_per_day rpd
LEFT JOIN active_users_per_day aupd ON rpd.visit_date = aupd.post_date
ORDER BY rpd.visit_date

Explicación de la consulta SQL

Parámetros

  • start_date y end_date: Definen el marco de tiempo para el análisis.
  • reader_post_read_count: Establece el umbral para considerar a alguien como “lector” según la cantidad de publicaciones que ha leído.
  • category_id: Opcional. Cuando se especifica, el informe se centra en una sola categoría, lo que permite un análisis más específico.

CTEs

  1. posts_by_user: Esta CTE identifica la cantidad de publicaciones creadas por cada usuario en cada día dentro del rango de fechas especificado. Es crucial para comprender qué usuarios contribuyen con contenido y cómo se distribuye esta actividad a lo largo del tiempo.
  2. posts_read_by_user: Esta CTE calcula la cantidad de publicaciones leídas por cada usuario en cada día, también dentro del rango de fechas especificado. Utiliza datos de visitas de usuarios para estimar el consumo de contenido, un indicador clave de participación.
  3. active_users_per_day: Basada en la CTE posts_by_user, esta CTE cuenta la cantidad de usuarios únicos que publicaron al menos una vez al día. Proporciona una instantánea diaria de los creadores de contenido activos.
  4. readers_per_day: Utilizando la CTE posts_read_by_user, esta CTE identifica a los usuarios que leyeron más que un número especificado de publicaciones (reader_post_read_count) sin publicar ellos mismos. Destaca a la mayoría silenciosa que participa leyendo.

Resultados

La salida final presenta una descripción general diaria de la participación del usuario, detallando la cantidad de lectores y usuarios activos (aquellos que publicaron) cada día.

Resultados de ejemplo

visit_date reader_count active_user_count
2024-01-02 150 25
2024-01-03 175 30
2024-01-04 160 20
2024-01-05 180 22
… … …
4 Me gusta